On Mar 27, 6:08 pm, BoniM wrote:
=IF(COUNT(B3:M3)2,IF(AND(ABS(INDIRECT("R"&ROW(3:3 )&"C"&(COUNT(B3:M3)-1),FA*LSE)-1)0.15,ABS(INDIRECT("R"&ROW(3:3)&"C"&(COUNT(B3:M3 )),FALSE)-1)0.15,AB*S(INDIRECT("R"&ROW(3:3)&"C"&(COUNT(B3:M 3)+1),FALSE)-1)0.15),"over","normal*"),"Three months required to evaluate")

This is assuming that your values are B3:M3

INDIRECT("R"&ROW(3:3)&"C"&(COUNTA(B3:M3)-1) has been substituted for your
cell references.
Row() is selecting row three and count is determining how many months you
have data for and subtracting 1 on the first adding 1 to the last to select
the appropriate three columns. The & is putting it all together in a R3C3
format that indirect reads as addresses that your formula can understand.
If you have less than three months, it will at this point display the text
"Three months required to evaluate", since you didn't specify.
Have fun!



"Jenny" wrote:
Hi,

I recently want to evaluate the accuracy of monthly forecast starting
from Jan'07.
If the Actual/forecast(A/F) 115% or <85% for consecutive 3 months in
most recent 3 months, I will mark it as "over", otherwise I will mark
it as "normal".
Table looks like this

Directors Jan(A/F) Feb(A/F) Mar(A/F) Apr(A/F) ...........Dec(A/
F) Evaluate
A 85.1% 73.1% .........................................
Over

Because it is a rolling forecast, I have to modify my "evaluate"
formula every month:

=IF(AND(ABS('JanA/F'-1)0.15,ABS('FebA/F'-1)0.15,ABS('MarA/
F'-1)0.15),"over","normal")

Is it possible to write a formula running formula before an empty
column ( I will leave "months after current month" blank ) that I
don't need to update formula but only add current month A/F data and
it will automatically shows most recent 3 months?

Thank a ton.- Hide quoted text -

- Show quoted text -

Thanks everyone.
Got a lot of good suggestions.