Running formula before an empty column(cell)

[Jenny]

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.

 

[Jenny]

Sorry, I mean" I need formula that
it will automatically shows most recent 3 months evaluate result"

 

[Domenic]

Define (Insert > Name > Define) BigNum as 9.99999999999999E+307, then
try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=IF(COUNT(B2:M2)>=3,IF(AND(ABS(INDEX(B2:M2,MATCH(BigNum,B2:M2)-3+1):INDEX
(B2:M2,MATCH(BigNum,B2:M2))-1)>0.15),"Over","Normal"),"<3 months")

Hope this helps!

 

[meatshield]

Sorry, I mean" I need formula that
it will automatically shows most recent 3 months evaluate result"

I added a column to the right of the "evaluate" column and entered the
formula
=ADDRESS(2,MATCH(TRUE,A2:L2="",0),4) (enter as array formula CTRL-
SHIFT-ENTER)
Then, in the "evaluate" column, I put the formula
=IF(AND(ABS(OFFSET(INDIRECT(N2),0,-3)-1)>0.15,ABS(OFFSET(INDIRECT(N2),
0,-2)-1)>0.15,ABS(OFFSET(INDIRECT(N2),0,-1)-1)>0.15),"over","normal")

This will cause an error if there are less than 3 cells to reference.
You can enter the "evaluate" formula as a named range (myFormula) then
use the formula
=IF(ISERROR(myFormula),"< 3 months",myFormula)

I hope that helps.

 

[Jenny]

Thanks Domenic
I tried your formulas. The first one works well is exactly what I
want. Can you tell me what dose bif number for...?

Thanks Meatshield

I also tried your formula but the results are always "Over".....

Jenny

 

[Domenic]

Thanks Domenic

You're very welcome!

Can you tell me what dose bif number for...?

Have a look at Aladin's explanation here...

http://www.mrexcel.com/board2/viewtopic.php?t=105725

Hope this helps!

 

[Guest]

=IF(COUNT(B3:M3)>2,IF(AND(ABS(INDIRECT("R"&ROW(3:3)&"C"&(COUNT(B3:M3)-1),FALSE)-1)>0.15,ABS(INDIRECT("R"&ROW(3:3)&"C"&(COUNT(B3:M3)),FALSE)-1)>0.15,ABS(INDIRECT("R"&ROW(3:3)&"C"&(COUNT(B3:M3)+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!

 

[meatshield]

Thanks Domenic
I tried your formulas. The first one works well is exactly what I
want. Can you tell me what dose bif number for...?

Thanks Meatshield

I also tried your formula but the results are always "Over".....

Jenny

Hmm.. When you enter the array formula, you'll have to make sure it is
pointing to the correct row.. The output will be something like "D2"
If the percentages are in row 5, the formula will not return the
correct result. Assuming that the formula will be looking for blank
cells in the same row as itself, you can change the formula to be:
ADDRESS(ROW(),MATCH(TRUE,A29:L29="",0),4)
entered as an array formula.
I hope that helps.

 

[Jenny]

Jenny

Hmm.. When you enter the array formula, you'll have to make sure it is
pointing to the correct row.. The output will be something like "D2"
If the percentages are in row 5, the formula will not return the
correct result. Assuming that the formula will be looking for blank
cells in the same row as itself, you can change the formula to be:
ADDRESS(ROW(),MATCH(TRUE,A29:L29="",0),4)
entered as an array formula.
I hope that helps.

Thanks for your explanation.
I try it again and found that it is because I didn't point it to the
right row.
Thanks again

 

[Jenny]

=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:M3)+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!









- Show quoted text -

Thanks everyone.
Got a lot of good suggestions.