Finding growth when negative values occur.

[Terry-J]

Hi,

I wondered if anybody could assist with the following:

I am running the following formula to calculate growth over a series of values over a number of years:

=(J2/B2)^(1/8)-1

The problem is if J2 or B2 are -ve values the formula returns #NUM!
B2 is the first value.
J2 is the last value.

Is there a way to ask the formula to skip B2 and move to C2 (assuming this is +ve) and use this value instead.
Similarly wit J2, could the previous value be used by the formula?

It is easier to understand in the attached file where I have put the calculations in.
Maybe there is a different way of calculating growth over a period. If both values are -ve, then the resultant growth value is +ve which is incorrect.

Terry

[José Augusto]

Try
=ABS(J2/B2)^(1/8)-1

[jason.b75]

Doing anything like that is going to give false results.

The most sensible method that I have seen suggests posting negatives as "Profit" or "Loss" with no % figure.

Try this in L2

=IFERROR(IF(AND($J$2<0,INDEX($B$2:$I$2,ROWS($L$2:L2))<0),"Loss",($J$2/INDEX($B$2:$I$2,ROWS($L$2:L2)))^(1/(8-ROWS($L$2:L2)+1))-1),"Profit")

Then fill down to L9 for the growth for all periods from 8 years down to 1. See if that gives the results you need.

[shg]

If you want growth over the rightmost stretch of positive numbers,

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Period	-8	-7	-6	-5	-4	-3	-2	-1	0		From Pd	To Pd	Growth
2	Income	(61)	991	1,260	959	176	752	943	958	884		3	10	-1.62%		L2: =IFERROR(LOOKUP(9E+307, COLUMN(A2:INDEX(2:2, M2))/(A2:J2 < 0) + 1), COLUMN(B2))
3	Income	(61)	(61)	1,260	959	176	752	943	958	884		4	10	-5.74%		M2: =LOOKUP(9E+307, COLUMN(A2:J2)/(A2:J2 > 0))
4	Income	(61)	(61)	1,260	959	176	752	(61)	958	884		9	10	-7.72%		N2: =(INDEX(2:2, M2)/INDEX(2:2, L2)) ^ (1/(M2-L2)) - 1
5	Income	959	991	1,260	959	176	752	943	958	884		2	10	-1.01%
6	Income	(61)	(61)	1,260	959	176	752	943	958	(61)		4	9	-5.33%
7	Income	(61)	(61)	1,260	959	176	752	943	(61)	(61)		4	8	-6.99%
8	Income	(61)	(61)	(61)	(61)	(61)	(61)	943	(61)	(61)		8	8	#DIV/0!

[Terry-J]

Thanks, that works well.
Terry

[Terry-J]

Thanks for that. It was a lot more than I was expecting.
Great stuff - I really appreciate the effort and time you have put in.

Best regards
Terry

[jason.b75]

Who was that reply directed at, Terry?

I'm not trying to criticise the suggestions made by the others, but both could return misleading results.

Using ABS() as suggested by Jose will skew the results, -1,000,000 in year 8 should be a greater growth than -100,000 when compared to the current positive figure, yet using ABS will show the opposite, the largest growth will come from the negative closest to 0 as the formula will evaluate the same figure as positive.

shg's method gives more accurate results by eliminating the negatives, but you would need to align any data headings with the results correctly. Showing the results against the wrong periods by skipping over values could again be misleading when you analyse the figures. Although this is by far the most accurate method suggested here.

My suggestion is becoming more unconvincing every time I look at it, there are certian pairings where it shows profit / loss where I think a % change should be shown, most notably when both values are negative.