This challenge has been ruining my morning:
What I am trying to do seems pretty simple, but I can't seem to find a
simple, elegant (or even complex, inelegant) solution. I am pulling
numbers from a database. Each row contains 10 years worth of data
(with the most recent number reported last). Unfortunately, I do not
have the same amount of data on each item, so many rows contain "0"
values in cells (time periods before reporting began). I am trying to
find the CAGR for the entire reported time period, starting with the
first data for each item. I am using a COUNTIF to determine number of
periods, but I am struggling mightily to come up with a formula
reporting the first value after the string of zeroes.
Any thoughts?
typical strings:

0 0 450 489 520 510 550 560

0 0 0 0 0 125 110 115

All I want is a formula that will return 450 for the first string of
numbers and 125 for the second.
Thanks much,
Mike

wrote...
....
numbers from a database. Each row contains 10 years worth of data
(with the most recent number reported last). Unfortunately, I do not
have the same amount of data on each item, so many rows contain "0"
values in cells (time periods before reporting began). I am trying to
find the CAGR for the entire reported time period, starting with the
first data for each item. I am using a COUNTIF to determine number of
periods, but I am struggling mightily to come up with a formula
reporting the first value after the string of zeroes.
....
0 0 450 489 520 510 550 560

0 0 0 0 0 125 110 115

All I want is a formula that will return 450 for the first string of
numbers and 125 for the second.

Since CAGR becomes undefined if there are any zeros between nonzero
values, there should only be leading zeros and possibly a terminal zero
(indicating the final value of the investment was zero, so -100% CAGR).
If so, try

=INDEX(rng,COUNTIF(rng,"0")+(INDEX(rng,COLUMNS(rng ))<0))

More generally, to find the first value not equal to X in rng, use the
array formula

=INDEX(rng,MATCH(1,--(rng<X),0))

Thanks so much - I am using the first formula, and it seems to be
working like a charm. Much appreciated!
-Mike

Harlan Grove wrote:
wrote...
...
numbers from a database. Each row contains 10 years worth of data
(with the most recent number reported last). Unfortunately, I do not
have the same amount of data on each item, so many rows contain "0"
values in cells (time periods before reporting began). I am trying to
find the CAGR for the entire reported time period, starting with the
first data for each item. I am using a COUNTIF to determine number of
periods, but I am struggling mightily to come up with a formula
reporting the first value after the string of zeroes.
...
0 0 450 489 520 510 550 560

0 0 0 0 0 125 110 115

All I want is a formula that will return 450 for the first string of
numbers and 125 for the second.

Since CAGR becomes undefined if there are any zeros between nonzero
values, there should only be leading zeros and possibly a terminal zero
(indicating the final value of the investment was zero, so -100% CAGR).
If so, try

=INDEX(rng,COUNTIF(rng,"0")+(INDEX(rng,COLUMNS(rng ))<0))

More generally, to find the first value not equal to X in rng, use the
array formula

=INDEX(rng,MATCH(1,--(rng<X),0))

Assuming your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<0,0))

ctrl+shift+enter, not just enter


" wrote:

This challenge has been ruining my morning:
What I am trying to do seems pretty simple, but I can't seem to find a
simple, elegant (or even complex, inelegant) solution. I am pulling
numbers from a database. Each row contains 10 years worth of data
(with the most recent number reported last). Unfortunately, I do not
have the same amount of data on each item, so many rows contain "0"
values in cells (time periods before reporting began). I am trying to
find the CAGR for the entire reported time period, starting with the
first data for each item. I am using a COUNTIF to determine number of
periods, but I am struggling mightily to come up with a formula
reporting the first value after the string of zeroes.
Any thoughts?
typical strings:

0 0 450 489 520 510 550 560

0 0 0 0 0 125 110 115

All I want is a formula that will return 450 for the first string of
numbers and 125 for the second.
Thanks much,
Mike

Thanks Teethless Mama - that worked too! Multiple ways to skin a cat!

Appreciate the help.


Teethless mama wrote:
Assuming your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<0,0))

ctrl+shift+enter, not just enter


" wrote:

This challenge has been ruining my morning:
What I am trying to do seems pretty simple, but I can't seem to find a
simple, elegant (or even complex, inelegant) solution. I am pulling
numbers from a database. Each row contains 10 years worth of data
(with the most recent number reported last). Unfortunately, I do not
have the same amount of data on each item, so many rows contain "0"
values in cells (time periods before reporting began). I am trying to
find the CAGR for the entire reported time period, starting with the
first data for each item. I am using a COUNTIF to determine number of
periods, but I am struggling mightily to come up with a formula
reporting the first value after the string of zeroes.
Any thoughts?
typical strings:

0 0 450 489 520 510 550 560

0 0 0 0 0 125 110 115

All I want is a formula that will return 450 for the first string of
numbers and 125 for the second.
Thanks much,
Mike