Average function

Question

I have a huge set of data which gets updated every month. I would like to check if the new data differs from previous months in %.

It look like this:

month name  data  
jan   551   2  
jan   552   20  
dec   553   12  
jan   553   13  
dec   554   9  
okt   555   2  
nov   555   2  
dec   555   2,5

So 5 names, up to 4 months and different data pr. month, pr. name.

I would like to make a formula that can tell me, if one of the names differ i % compared to previous months. For instance I would like it to say that "name" 555 in dec differs by 25% compared to the average for that customer.

thanks,
Anders

Well I guess that I'm not too good to explain myself. So I'll try to be a bit more clear.

I'm doing this as an excel function.

So the formula I'm looking for should calculate pr. name the average figures (avgN). This of course changes each month. And the formula should find the monthly difference compared to the average.

I have about 2.000 rows with 900 different names.

Dónal · Answer 1 · 2010-09-07T12:10:27.673

Assume:

avgN = average amount for customer n
currN = current (monthly) amount for customer n
delta = tolerance expressed as a decimal (.25 in your example)

Then the formula

=abs((currN - avgN) / avgN)

will return the percentage difference between this month's amount and the average. If you want to test whether this exceeds the tolerance (delta) use the expression above as the first argument to the if worksheet function, e.g.

=if(abs((currN - avgN) / avgN) > delta, "tolerance exceeded", "within tolerance")

It's not clear from your question whether you want to do this in VBA code, or in a worksheet function. If the former, then you'll need something like this

Sub checkTolerance()

    Dim percentageChange As Double
    Dim currN As Double
    Dim avgN As Double
    Dim delta As Double

    ' Set the values of the variables above from somewhere (worksheet cell, user input, etc.)

    percentageChange = Abs((currN - avgN) / avgN)


    If percentageChange > delta Then 
        ' Do something when tolerance is exceeded
        MsgBox "tolerance exceeded"     

    Else
        ' Do something when amount is within tolerance
        MsgBox "within tolerance"
    End If 

End Sub

P.S. please don't tell anyone that I know VBA :) – Dónal Sep 07 '10 at 12:13 — Dónal, Sep 07 '10 at 12:13

score 0 · Answer 2 · answered Sep 07 '10 at 14:25

=(SUM(($A$2:$A$9=A2)*($B$2:$B$9=B2)*($C$2:$C$9))-(SUM(($B$2:$B$9=B2)*($C$2:$C$9))/SUM(--($B$2:$B$9=B2))))/SUM(($A$2:$A$9=A2)*($B$2:$B$9=B2)*($C$2:$C$9))

This is an array formula and must be entered with Control+Shift+Enter, not just Enter. If your first line of data starts in A2, this formula goes in D2 and fill down to the extent you have data.

SUM(($A$2:$A$9=A2)*($B$2:$B$9=B2)*($C$2:$C$9))

This part sums all the data that has the same name and month as the row you're on.

(SUM(($B$2:$B$9=B2)*($C$2:$C$9))/SUM(--($B$2:$B$9=B2)))

This part takes the average (sum/count) of all the data that has the same name as the row you're on regardless of month.

For dec, 553, 12, you'll get -4.17%. The average for 553 is 12.5 and dec is 4.17% less than the average.

Average function

2 Answers2