Quote:
Originally Posted by GraemeSS
I want to know percentage change from last month (C6-D6)/C6.
|
I'm not sure this'll give that, shouldn't it be (
D6-
C6)/C6 ?
I've assumed later months are to the right.
This can be shortened to:
D6/C6-1
Anyway, I leave that to you by tweaking that bit in these formulae.
Using the same basis for finding the last month:
Code:
=OFFSET(B6,0,COUNTIF($C6:$AF6,">.00"))/OFFSET(B6,0,COUNTIF($C6:$AF6,">.00")-1)-1
This will give the wrong answer if you have any or more of the following in the cells to the left of last entry:
- spaces/blank cells
- errors
- zeroes
- negative numbers
- text
Significantly safer, but longer and more difficult to maintain:
Code:
=(INDEX($A6:$AF6,AGGREGATE(14,4,(NOT(ISBLANK($B6:$AF6))*COLUMN($B6:$AF6)),1))/INDEX($A6:$AF6,AGGREGATE(14,4,(NOT(ISBLANK($B6:$AF6))*COLUMN($B6:$AF6)),1)-1))-1