Find LATEST non-zero value in a row

[vanderwel]

Hello,

I am trying to figure out how to display the latest non-zero value in a row. Note, I am not looking for the last value (there is plenty of help here and else where on that) but the latest/most recent. Here is an example:

Source Data <tbody> </tbody>	19.5 <tbody> </tbody>	149.1 <tbody> </tbody>	0 <tbody> </tbody>	0	23.4 <tbody> </tbody>	0 <tbody> </tbody>	0 <tbody> </tbody>	0 <tbody> </tbody>
Desired Outcome <tbody> </tbody>	19.5 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1 <tbody> </tbody> <tbody> </tbody>	149.1	23.4 <tbody> </tbody>	23.4 <tbody> </tbody>	23.4 <tbody> </tbody>	23.4 <tbody> </tbody>
Attempt 1 <tbody> </tbody>	19.5 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1	23.4 <tbody> </tbody>	0 <tbody> </tbody> <tbody> </tbody>	0 <tbody> </tbody>	0 <tbody> </tbody>
Attempt 2 <tbody> </tbody>	19.5 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1	23.4 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1 <tbody> </tbody>	149.1 <tbody> </tbody>

<tbody>
</tbody>

In Attempt 1 I used the formula =IF(B1=0,OFFSET($B$1,0,(COUNT($B1:B1)-COUNTIF($B1:B1,0))-1),B1) dragging from left to right (so the COUNT ranges were increasing with each cell)

In Attempt 2 I used the formula = =IF(B1=0,INDEX($B1:B1,,MATCH(0,$B1:B1,0)-1),B1) again dragging from left to right.


Any ideas how I can achieve my desired outcome?

Thank you!

 

[CyrusTheVirus]

I'm not entirely sure what you are trying to do, but the below formula should match your desired outcome...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Source Data	19.5	149.1	0	0	23.4	0	0	0
2	Desired Outcome	19.5	149.1	149.1	149.1	23.4	23.4	23.4	23.4
3	Formula	19.5	149.1	149.1	149.1	23.4	23.4	23.4	23.4
Sheet2