Quote:
Originally Posted by p45cal
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
|
Thank you so much p45cal! The formula works perfectly (and of course you were right I had made a mistake in my 5th grade % formula).
Just to give you some background, I am setting up a report to show telephony call, duration and cost data. Then from that average duration and average cost per call. I want to have charts for a visual representation.
I have tried to set up my sheet to populate all the different tabs from a single source for two and a half years.
Problem is when I have formulas looking for data from the other sheets it assumes the next column has zero and I get a Div/0 error. How do I get the formula to remove the zero if there is no data?
In your debt, Graeme