Average annual growth rate excluding non numeric values.

[AYS]

Hi,


I've 20 cells in a row (A1:T1) with numbers/values in. I was wondering is there a formula to calculate the "average annual growth rate" of the most recent 10 numbers out of the total 20 cells, that excludes any non numeric values.

	A	B	C	D	E	F	G	H	I	J	K	L	MN	N	O	P	Q	R	S	T	U
1	-	-	1.180	1.030	1.580	0.980	0.380	1.840	0.640	2.690	2.95000	2.530	5.520	2.350	3.110	2.800	1.560	2.330	1.630	0.85000
2	-	-	-	-	-	-	-	-	-	-	9.68000	13.120	17.340	15.990	23.140	17.830	8.420	8.170	26.920	28.69500
3	-	-	-	-	-	-	-	-	0.030	0.014	0.04300	-0.009	0.108	0.089	0.107	0.120	0.042	0.065	0.130	0.05000
4	-	-	-	-	-	-	-	-	0.105	0.220	0.37400	0.406	0.793	0.829	1.061	1.213	1.494	1.307	1.658	-
5	-	-	-	-	-	-	-	-	1.320	2.170	4.23000	6.095	2.278	2.045	-	1.444	-0.988	0.845	-	-
6	-	-	-	-	-	-	-	-10.310	3.257	0.570	144.91200	3.177	1.964	1.750	0.442	0.520	0.670	-3.058	-3.058	-7.10300
7	-	-	-	-	-	-	-	16.322	0.284	0.523	0.55800	0.667	0.441	0.285	0.261	-0.020	0.036	0.108	-2.122	-
8	-	-	-	-	-	-	-	-	-	4.890	708.04500	1111.500	4779.770	2080.073	2247.723	3342.930	2367.208	1698.905	1375.490	1595.46000
9	-	-	-88.399	247.743	137.723	-	-	-	-	-	-	-	-	2.320	7.741	1.658	2.881	2.920	2.683	-9.09100
10	-7.020	-5.572	0.011	1.238	278.000	-	-	-	-	-	-	-	-	-	2.233	1.100	1.140	0.820	0.670	0.18000
11	-	-	-	-	-	-	-	-	-	0.010	0.01000	0.020	-0.005	-0.010	0.020	1.000	-0.004	-	-0.016	-0.02000
12	-	-	-	-	-	-17.681	-10.772	1.160	-0.262	14.954	21.15500	31.593	41.316	29.773	31.874	27.643	27.853	12.882	-46.087	-13.52700
13	-	-	-	-	-	-	-	-	0.179	0.278	0.88600	0.712	0.385	0.590	0.020	0.640	0.340	0.205	0.028	0.31600
14	-	-	-	-	-	-	-	-	-	21.010	19.16000	34.170	13.030	19.280	9.847	7.011	11.029	10.338	0.150	-26.55200
15	-	-	-	-	-	-	-	-	-	-	216.99900	12.341	-0.009	0.059	-0.028	0.183	0.239	0.354	-	-

This is the average annual growth rate formula in U1.

=SUM((M2/L2-1)+(N2/M2-1)+(O2/N2-1)+(P2/O2-1)+(Q2/P2-1)+(R2/Q2-1)+(S2/R2-1)+(T2/S2-1)+(U2/T2-1))/9

It fine when there's no gaps but when there's gaps I can't make it work.

Cell T1 is the most recent value with A1 being the oldest value.


Thanks.

[Bob Phillips]

Try this

=AVERAGE(INDEX(A1:T1,1,IFERROR(LARGE(IF(A1:T1<>"-",COLUMN(A1:T1),MIN(IF(A1:T1="-",99999,COLUMN(A1:T1)))),10),1)):T1)

It is an array formula, so Ctrl-Shift-Enter

[AYS]

Can you post your workbook Go Advanced/Manage Attachments

Thanks for the directions for uploading files.

[AYS]

Thanks xld.

When I punched that into column U the figures looked a little out. I am a little burnt out though so maybe it's me.

I've now added a workbook so maybe that will help better than the copy/paste earlier.



Edit,

After a bit of a break xld, I now know what your formula does. It's giving me the average of a maximum 10 of the most recent figures. Not what I'm after but it will surely coming in handy for other things in the future. Thanks.

[Bob Phillips]

Can you explain to me the difference between what you originally said ...

I was wondering is there a formula to calculate the "average annual growth rate" of the most recent 10 numbers out of the total 20 cells, that excludes any non numeric values.

and what you say my formula gives ...

I now know what your formula does. It's giving me the average of a maximum 10 of the most recent figures.

That seems the same to me.

[AYS]

Can you explain to me the difference between what you originally said ...

I was wondering is there a formula to calculate the "average annual growth rate" of the most recent 10 numbers out of the total 20 cells, that excludes any non numeric values.

and what you say my formula gives ...

I now know what your formula does. It's giving me the average of a maximum 10 of the most recent figures.

That seems the same to me.

No worries,

I'm looking for the Average annual growth rate of the 10 most recent numbers, excluding non numeric values. This formula works out the difference between the side by side years and then averages it.

=AVERAGE((L1/K1-1),(M1/L1-1),(N1/M1-1),(O1/N1-1),(P1/O1-1),(Q1/P1-1),(R1/Q1-1),(S1/R1-1),(T1/S1-1))



Your formula works out the average of the 10 most recent numbers, excluding non numeric values.

=AVERAGE(K1:T1)

[AYS]

Here's a link to another forum I've posted this question.

(Can't get this link to post?)

[p45cal]

(Can't get this link to post?)

Try missing off the http:// bit.

[p45cal]

I'm looking for the Average annual growth rate of the 10 most recent numbers, excluding non numeric values. This formula works out the difference between the side by side years and then averages it.

=AVERAGE((L1/K1-1),(M1/L1-1),(N1/M1-1),(O1/N1-1),(P1/O1-1),(Q1/P1-1),(R1/Q1-1),(S1/R1-1),(T1/S1-1))

I think this will give you misleading results:
You want the annual growth and you say the years are side-by-side. So if there is a missing value (a missing year) then the two values either side of that missing value represent 2 years' growth. If there are 3 missing values, then the two values either side of that represent 4 years' growth, so you can't use the likes of (N5/P5-1) to represent one year's growth, because it occurred over 2 or more years.
Wouldn't you get more meaningful results if you took the value 10 years ago, and the value now, work out the growth over that 10 year period (don't subtract 1) and raise it to the power 1/10? That should give you the annual (compounded) effective rate of growth. If you haven't got exactly 10 years, then choose the latest value and one that is as close as you can to 10 years ago and raise to the power 1/n, n being the number of years between those two values.
On top of that, if you don't have the most recent values (rows 4,5,7 & 15) then you should state which is the last year with figures at the same time as stating the average growth rate.

For row 1 that would be:

=(T1/K1)^(1/9)-1

As an aside, with a contiguous 10 values as you have in row 1, your formula can be simplified to:

=AVERAGE(L1:T1/K1:S1-1)

or array-entered:

=AVERAGE(L1:T1/K1:S1)-1

…now if xld can work his magic and tweak his formula?

[AYS]

I think this will give you misleading results:
You want the annual growth and you say the years are side-by-side. So if there is a missing value (a missing year) then the two values either side of that missing value represent 2 years' growth. If there are 3 missing values, then the two values either side of that represent 4 years' growth, so you can't use the likes of (N5/P5-1) to represent one year's growth, because it occurred over 2 or more years.
Wouldn't you get more meaningful results if you took the value 10 years ago, and the value now, work out the growth over that 10 year period (don't subtract 1) and raise it to the power 1/10? That should give you the annual (compounded) effective rate of growth. If you haven't got exactly 10 years, then choose the latest value and one that is as close as you can to 10 years ago and raise to the power 1/n, n being the number of years between those two values.
On top of that, if you don't have the most recent values (rows 4,5,7 & 15) then you should state which is the last year with figures at the same time as stating the average growth rate.

Side by side when there's been figures posted for that year but if there's been no figures posted for that year I want it to take the next value beside that, excluding any non numeric values. Sometimes there's no figures for a year and I have to go with the year before.

I do have a compound annual growth rate formula that calculates the most recent 10 figures, excluding non numeric values. It's an average annual growth rate that I'm after.

[p45cal]

Sometimes there's no figures for a year and I have to go with the year before.

That's the bit I disagree with.

[AYS]

I've added a second sheet to the workbook with all the rows compressed as though the non numeric have been removed. On sheet 2 column V I have the results of the formula I need. You can flick between the two sheets, like on an over lay.


Edit.

Workbook changed. The first one was a different workbook.

[AYS]

That's the bit I disagree with.

I know totally what you mean but it's what I need.

[AYS]

Great, I like this simplification for a continuous 10.

=AVERAGE(L1:T1/K1:S1-1)

[AYS]

Here's my 10 most recent figures compound annual growth rate formula, excluding non numeric values, (It an array). Maybe this will help someone with the average annual growth rate formula. It's kind of cool as it keeps the sheet looking smart by returning a blank if it's an error.

=IFERROR((LOOKUP(10^10,A1:T1)/INDEX(A1:T1,LARGE(IF(A1:T1<>"-",COLUMN(A1:T1)),10)))^(1/(10-1))-1,"")

[p45cal]

In the attached is a user defined function:
Capture3.JPGThe RightmostCount value is the maximum number of values (not years) you want to use in the calculation. If there are fewer than this number of values it will use as many as it can find in Myrange.
I've used it in column V of Sheet1.
I've also put what I think are not-quite-as-misleading figures (they a show a figure without stating which years are used) in column X.

The UDF is garbage. If someone uses this value as an average annual growth figure to help sell an investment product, it's a lie, for at least 2 reasons.


I've also put what I think are not-quite-as-misleading figures in column X (they don't show which years are used).

[AYS]

The RightmostCount value is the maximum number of values (not years) you want to use in the calculation. If there are fewer than this number of values it will use as many as it can find in Myrange.
I've used it in column V of Sheet1.
I've also put what I think are not-quite-as-misleading figures (they a show a figure without stating which years are used) in column X.

The UDF is garbage. If someone uses this value as an average annual growth figure to help sell an investment product, it's a lie, for at least 2 reasons.


I've also put what I think are not-quite-as-misleading figures in column X (they don't show which years are used).

Interesting p45cal. I'll have to play about with this as I've not used this type of function before.

I know what you are saying about a misleading value. Don't be worrying I'm not selling or being sold an investment products based on what I'm looking for. This will only be a visual highlighter/identifier, to help filter out the wheat from the chaff.

[AYS]

The links to the other forums I've asked this question.


www.excelforum.com/excel-formulas-and-functions/1188422-average-annual-growth-rate-that-excludes-non-numeric-values.html

www.eileenslounge.com/viewtop...209486#p209486

[p45cal]

That eileenslounge link didn't work; I think this will: http://www.eileenslounge.com/viewtopic.php?f=27&t=27007

[AYS]

That eileenslounge link didn't work; I think this will: http://www.eileenslounge.com/viewtopic.php?f=27&t=27007

This puzzle has been solved over on the Excel Forum by Glen.


He got there by using other additional cells but it works fine. He say most likely I would need VBA to get a more elegant solution but I'm happy with what he's done. If anyone wants to work on a VBA that's fine.


Thanks everyone.