Find LATEST non-zero value in a row

V

vanderwel

New Member
Joined
Mar 16, 2015
Messages
4
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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="width: 112, align: center"]Source Data[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]19.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]0[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="width: 112, align: center"]Desired Outcome[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]19.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]149.1[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="class: xl65, width: 112, align: center"]Attempt 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]19.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]149.1[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: right"][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="class: xl65, width: 112, align: center"]Attempt 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]19.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]149.1[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]23.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, align: center"]149.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
I'm not entirely sure what you are trying to do, but the below formula should match your desired outcome...

Excel Workbook
ABCDEFGHI
1Source Data19.5149.10023.4000
2Desired Outcome19.5149.1149.1149.123.423.423.423.4
3Formula19.5149.1149.1149.123.423.423.423.4
Sheet2
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Lowest value in range of cells excluding this row
2
Replies
10
Views
838
Peter_SSs
Peter_SSs
Q
  • Question Question
Show Decimals until first non-zero decimal is shown
2
Replies
13
Views
225
myall_blues
myall_blues
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
270
Peter_SSs
Peter_SSs
D
  • Question Question
Filling Zero cells with Non Zero cell values
Replies
1
Views
1K
FormR
FormR
D
  • Solved
Flag the latest correct value from grouped rows with criteria
Replies
5
Views
388
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top