Compare last value in an array with the average of the previous ones

[Sparcot]

Hello. Help needed: need a formula to compare the last input value in an array with the average of the previous ones. Example:

| J | F | M | A | M | J | J | A | S | O | N | D | Formula column |
| 2 | 3 | 4 | 1 | 5 | 4 |

In this case the formula should let me know that in June the value was higher than the average of Jan - May [ 4>(2+3+4+1+5)/5 ]. If it's possible the formula to return an index (-1, 0, 1) to apply some conditional formatting in formula column.

 

[ravisingh]

if your data is in a row starting A2 (say A2:L2), select B3 and input the following formula:
=(AVERAGE($A2:A2)>B2)-(AVERAGE($A2:A2)<b2)
<b2)
Copy this formula across to the extent of the data area in row 3

It will return 1 if the previous months' avg is greater than the value for the month of interest, 0 if equal and -1 if less

if your data is in a col starting B1 (say B2:B12, select B3 and input the following formula:
=(AVERAGE(B$1:B1)>B2)-(AVERAGE(B$1:B1)<b2) and="" copy="" down.<="" b2)
</b2)></b2)
</b2)

 

[ravisingh]

Please nix the previous reply...there was some error in pasting formulas

if your data is in a row starting A2 (say A2:L2), select B3 and input the following formula:
=(AVERAGE($A2:A2)>B2)-(AVERAGE($A2:A2)>B2)
<b2)<b2)
<b2)
<b2)
Copy this formula across to the extent of the data area in row 3

It will return 1 if the previous months' avg is greater than the value for the month of interest, 0 if equal and -1 if less

if your data is in a col starting B1 (say B2:B12), select C2 and input the following formula:
=(AVERAGE(B$1:B1)>B2)-(AVERAGE(B$1:B1)<B2)
<b2)<b2)
and copy down
</b2)<b2)
</b2)

</b2)
</b2)<b2)

 

[Sparcot]

Thanks for the reply, but it's not what I actually need. In a month i will input the value for the next month for example July. And it already should compare July value to Jan - Jun...

 

[Rick Rothstein]

I believe this formula will give you the result you asked for...

=SIGN(LOOKUP(9E+99,A2:L2)-(SUM(A2:L2)-LOOKUP(9E+99,A2:L2))/(COUNT(A2:L2)-1))

 

[ravisingh]

With ref to column data format, you would use the following formula in the conditional formatting dialog box for cell C2
=IF(ISBLANK(B3),"",(AVERAGE(B$1:B1)>B2)-(AVERAGE(B$1:B1)<b2))'
is less than symbol B2))
<b2))
(gets truncated at less than symbol)
<b2))
<b2))
<b2))
<b2))
and set the format conditions on basis of result of this formula (-1,0,1)
Copy the formula+format of Cell C2 down the data range

Similarly for data on row format</b2))
</b2))
</b2))
</b2))
</b2))></b2))'

 

[Sparcot]

I believe this formula will give you the result you asked for...

=SIGN(LOOKUP(9E+99,A2:L2)-(SUM(A2:L2)-LOOKUP(9E+99,A2:L2))/(COUNT(A2:L2)-1))

Thanks a lot, Rick! This one works perfect!

 

[Sparcot]

I believe this formula will give you the result you asked for...

=SIGN(LOOKUP(9E+99,A2:L2)-(SUM(A2:L2)-LOOKUP(9E+99,A2:L2))/(COUNT(A2:L2)-1))

Actually, I've used a ROUND with 2 digits to eliminate the period after the comma [EX: 3.7-3.6(6)]:

=SIGN(ROUND(LOOKUP(9E+99,A2:L2)-(SUM(A2:L2)-LOOKUP(9E+99,A2:L2))/(COUNT(A2:L2)-1),2))[/QUOTE]