Calculating an AVG based on an array

Question

I am attempting to calculate an overall average of 6 items. Some of these items may be zero therefore would not come into play when calculating the average. Averaging completely confuses me and am having a difficult time working through this.

My Code as I currently have it:

     Dim a,
        af1, af2, af3, af4, af5, af6,
        t7, t8, t9, t10, t11, t12 As Decimal

        'Adjusted Fuel Cost per line
    af1 = CDec(IIf(tbAdjCostPerGal1.Text.Trim = "", 0D, tbAdjCostPerGal1.Text.Trim))
    af2 = CDec(IIf(tbAdjCostPerGal2.Text.Trim = "", 0D, tbAdjCostPerGal2.Text.Trim))
    af3 = CDec(IIf(tbAdjCostPerGal3.Text.Trim = "", 0D, tbAdjCostPerGal3.Text.Trim))
    af4 = CDec(IIf(tbAdjCostPerGal4.Text.Trim = "", 0D, tbAdjCostPerGal4.Text.Trim))
    af5 = CDec(IIf(tbAdjCostPerGal5.Text.Trim = "", 0D, tbAdjCostPerGal5.Text.Trim))
    af6 = CDec(IIf(tbAdjCostPerGal6.Text.Trim = "", 0D, tbAdjCostPerGal6.Text.Trim))

            'Truck Gallons Purchased Related
    t7 = CDec(IIf(tbTrkGalsPurch1.Text.Trim = "", 0D, tbTrkGalsPurch1.Text.Trim))
    t8 = CDec(IIf(tbTrkGalsPurch2.Text.Trim = "", 0D, tbTrkGalsPurch2.Text.Trim))
    t9 = CDec(IIf(tbTrkGalsPurch3.Text.Trim = "", 0D, tbTrkGalsPurch3.Text.Trim))
    t10 = CDec(IIf(tbTrkGalsPurch4.Text.Trim = "", 0D, tbTrkGalsPurch4.Text.Trim))
    t11 = CDec(IIf(tbTrkGalsPurch5.Text.Trim = "", 0D, tbTrkGalsPurch5.Text.Trim))
    t12 = CDec(IIf(tbTrkGalsPurch6.Text.Trim = "", 0D, tbTrkGalsPurch6.Text.Trim))

            'Calculate ADJUSTED Average Cost of ALL Fuel plus Fuel Card fee and any Fuel Discounts this Load
    Try
        If af1 > 0 Then
            a = (af1 + af2 + af3 + af4 + af5 + af6) / (t7 + t8 + t9 + t10 + t11 + t12)
            tbFuelCostAdj.Text = a.ToString("C3")
        Else
            a = 0D
            tbFuelCostAdj.Text = a.ToString("C3")
        End If
    Catch ex As Exception
        'If a calculation error occurs, show Error message box
        Dim frm As New MeMsgCalcError(ex, "'Adjusted Cost of Fuel this Load' Calculation Error." & vbCrLf & "Lines 644-652")
        frm.Show()
    End Try

This is the relevant portion of my code where I am attempting to get the average.

I have as an example:

tbAdjCostPerGal1 = $2.259
tbAdjCostPerGal2 = $2.469
tbAdjCostPerGal3 = $0.000
tbAdjCostPerGal4 = $0.000
tbAdjCostPerGal5 = $0.000
tbAdjCostPerGal6 = $0.000
tbTrkGalsPurch1 = 100.000
tbTrkGalsPurch2 = 93.000
tbTrkGalsPurch3 = 0.000
tbTrkGalsPurch4 = 0.000
tbTrkGalsPurch5 = 0.000
tbTrkGalsPurch6 = 0.000

Based on the info above, only the 1st 2 entries (tbAdjCostPerGal) would be added and then averaged since items 3-6 are a 0 value ending with an end result of $2.364 cost per gallon average.

So in summary, I need to total and average where all tbAdjCostPerGal.text > 0. I believe if I average just the tbAdjCostPerGal entries this would give me the answer I am looking for. There will always be 6 entries per record.

I know I need to create some way to make a variable to look at the tbAdjCostPerGal entries, totalling only those with a value greater than 0 and this is where I lack knowledge. Can someone assist me in the proper formatting of this please?

Zero is a legitimate value - why would it not come into play? — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented May 14, 2017 at 23:44
It looks like this is average Fuel prices. I'm guessing a price of $0.0 for the fuel means that there is no price recorded rather than the fuel is free. (where do I have to go to fill up with THAT fuel??) So in the case of calculating the average fuel cost you really DO want to exclude the $0 values. IN this case a better "no price recorded" semaphore SHOULD be used. But because it's not we have to work with a $0.0 semaphore. — David
– David, Commented May 14, 2017 at 23:50

Slai · Accepted Answer · 2017-05-15 02:37:10Z

1

I am guessing that you need the total price divided by the number of gallons. For example:

Total Price = $2.259 * 100.000 + $2.469 * 93.000 + $0.000 * 0.000 + ... = $455.517

Average per Gallon = Total Price / # of Gallons = 455.517 / (100 + 93 + ...) = 2.36019170984

For average of values that are not 0, you can divide the sum by the number of values that are not 0:

Dim array = { af1, af2, af3, af4, af5, af6 }

Dim count = array.Count(Function(d) d <> 0)

Dim average = IIF(count = 0, 0, array.Sum() / count)

or filter out the 0 values:

tbFuelCostAdj.Text = array.Where(Function(d) d <> 0).Average.ToString("C3")

edited May 15, 2017 at 2:37

answered May 15, 2017 at 0:22

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Etling Over a year ago

Your guess would be correct. Yes, a 0.000 or $0.000 would mean no price or fuel was purchased on that line. Otherwise, like you, I would be purchasing fuel there every day!! The default value is always 0 but cannot figure that in because then the divider would always be 6 and that is where i am failing. I need it only to divide by the number where the value is >0.

John Etling Over a year ago

This works well with one exception... if a record has no fuel purchased, whatsoever, the code crashes... Divide by Zero Error. So need to work in a check to make sure something exists prior to working the calc. I tried adding inside a try statement: if af1 > 0 then.... but that made no difference and still get the Divide by zero error.

Slai Over a year ago

@JohnEtling so check if the divisor is 0 before dividing

John Etling Over a year ago

Reworked the TRY statement and problem resolved. Thanks Slai for the resolve.

Collectives™ on Stack Overflow

Calculating an AVG based on an array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related