Dynamic Range

[Littlemalky]

Ok, I need to be able to return the left-most cell value that is not a 0 so I can plug this into a CAGR calculation. I'm trying to work with a dynamic offset, but can't seem to get it to work properly to omit zero in the range. I need one formula that references the entire range of values in row 2. Row 1 represents the years.

Example, I need to be able to return 100 with a formula. However, if 2018 had a value other than zero, then I would need to return that instead. Any advice?

2018	2019	2020	2021	2022	2023
0	100	200	300	400	500

<tbody>
</tbody>

 

[mrshl9898]

Try this:

=LARGE(A2:F2,COUNTIF(A2:F2,"<>"&0))

 

[StephenCrump]

Or this (given that values can presumably fall lower than the start value):

=INDEX(A2:F2,MATCH(TRUE,INDEX(A2:F2<>0,),))

 

[Littlemalky]

Thank you! I ended up finding SMALL(H8:M8,COUNTIF(H8:M8,0)+1) online somewhere. It seems to be working for me so far. Do you guys see any problems with it?

 

[StephenCrump]

Do you guys see any problems with it?

What about if the 2019 value is 100, but in 2020 (or some later year) it falls to, say, 95?

 

[Aladin Akyurek]

What about if the 2019 value is 100, but in 2020 (or some later year) it falls to, say, 95?

...and if there are formulas in the range or text in the range for some reason:

Either control+shift+enter...

=INDEX(A2:F2,MATCH(TRUE,ISNUMBER(1/A2:F2),0))

Or just enter...

=INDEX(A2:F2,MATCH(TRUE,INDEX(ISNUMBER(1/A2:F2),0),0))

 

[Littlemalky]

Good point! Thank you!