CODEWITHSUNDEEP
excelexcel-formula
Alon_T
Alon_T

Reputation: 1440

CAGR on Dynamic Cells

I have a column of 12 rows as follows:

enter image description here

For simplicity let's assume that the column is A and the values are in rows 1-12 (ignoring the first two rows).

I need to calculate the CAG, for which the formula is: 

(end value/beginning value)^1/(# of years) -1

Here it would be (38026/2402)^(0.25)-1. The small thing is that for every column I have, the first non zero value appears in a different row number. This both affects the years # in the formula, as well as the value in the beginning period.

What formula would give me that?

Upvotes: 1

Views: 307

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

With the following data layout:

enter image description here

You can find:

B1 - position of first non value with:

=MATCH(1,--(A1:A12<>0),0)

C1 - position of last value (exluding #REF!) with:

=MATCH(9.999999E+307,A1:A12)

D1 - CAGR value with:

=(INDEX(A1:A12,C1)/INDEX(A1:A12,B1))^(1/(C1-B1))-1

Or:

=RATE(C1-B1,,-INDEX(A1:A12,B1),INDEX(A1:A12,C1))

Second method was found here.

Upvotes: 1

Related Questions