how to get last two rows??

[hardik]

hi all,

I want to calulate percentege based on last two rows. so how can i
do this.

my excel shit is as under.

Year May June July

2004 $7,430.00 $47,526.00 $58,366.00
2005 $8,397.00 $56,308.00 $53,928.00
2006 $0.00 $0.00 $0.00


% 13.01 18.47 -7.60

Difference $967.00 $8,782.00 -$4,438.00

now it is calculating percentage increase/decrease between 2004/05/06.
if there is 0 in 2006 than it calulates based on 2004/05.
if there is 0 in 2004 than it calulates based on 2005/06.

but now if someone add row for 2007 than it should calculate the
difference and % of that last two rows based on 2006/07.

so plz help me regarding that.

thx in advance.
-hardik

 

[Bondi]

Hi,

There is proberly an easier way to do it but you can use CountIf() and
Ofsett(),
Asuming your data for May 2004 is en cell B3 this should give you the
pct change

=(OFFSET(B3,COUNTIF(B3:B6,">0")-1,0)-(OFFSET(B3,COUNTIF(B3:B6,">0")-2,0)))/(OFFSET(B3,COUNTIF(B3:B6,">0")-2,0))

The difference can be calculated with the same functions:

=(OFFSET(B3,COUNTIF(B3:B6,">0")-1,0))-(OFFSET(B3,COUNTIF(B3:B6,">0")-2,0))

Best regards
Bondi

 

[Aladin Akyurek]

It seems you use $0.00 as default value for the last year. Can you leave
it blank instead?