PDA

View Full Version : [SOLVED] Average annual growth rate excluding non numeric values.



AYS
06-11-2017, 02:10 AM
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
06-11-2017, 05:55 AM
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
06-11-2017, 06:28 AM
Can you post your workbook Go Advanced/Manage Attachments

Thanks for the directions for uploading files.

AYS
06-11-2017, 06:45 AM
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
06-11-2017, 10:29 AM
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
06-11-2017, 11:05 PM
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
06-11-2017, 11:34 PM
Here's a link to another forum I've posted this question.

(Can't get this link to post?)

p45cal
06-12-2017, 03:19 AM
(Can't get this link to post?)Try missing off the http:// bit.

p45cal
06-12-2017, 03:37 AM
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
06-12-2017, 03:45 AM
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
06-12-2017, 04:00 AM
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
06-12-2017, 04:03 AM
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
06-12-2017, 04:05 AM
That's the bit I disagree with.


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

AYS
06-12-2017, 04:16 AM
Great, I like this simplification for a continuous 10.

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

AYS
06-12-2017, 04:36 AM
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
06-12-2017, 05:46 AM
In the attached is a user defined function:
19473The 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
06-12-2017, 06:41 AM
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
06-13-2017, 03:53 AM
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 (http://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
06-13-2017, 04:54 AM
That eileenslounge link didn't work; I think this will: http://www.eileenslounge.com/viewtopic.php?f=27&t=27007

AYS
06-15-2017, 12:59 AM
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.

p45cal
06-15-2017, 03:50 AM
If anyone wants to work on a VBA that's fine.I did, in msg #16; it gives the results you wanted.

AYS
06-15-2017, 04:00 AM
Sorry p45cal I thought that was something different again.

To be honest, I don't really know what a VBA is. I'm still only at the formula level of Excel.