(Excel equation) Find last non-zero value in a column. Apply it in the same column. Keep applying it when there's a zero in another column.

MASGaming

New Member
Joined
Nov 5, 2017
Messages
7
Tried for the D column:

#=IF(E5 <= 0, LOOKUP(2,1/($D$4:$D$33<>0),$D$4:$D$33), E5)#

I got this answer from this website but I'm unable to make it work properly. The sum in I4 doesn't calculate properly, either.

More details of issue in image:

GShjPN7.png



Thanks for all your help!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
More information:

I'd like column D (OLD WORD COUNT) to grab the last non-zero in its column when there's a zero in column E (NEW WORD COUNT.) That way, writers can miss a day, or a few, and when they put in a NEW AMOUNT, the spreadsheet will grab their last non-missed day from OLD WORD COUNT.

I'm using Excel 2013, Win 10.

The code I am using now for the OLD WORD COUNT column
Code:
[COLOR=#333333]=IF(E5 <= 0, LOOKUP(2,1/($D$4:$D$33<>0),$D$4:$D$33), E5)[/COLOR]
doesn't grab the last non-zero value.

Example:
DATEOLD
WORD COUNT
NEW
WORD COUNT
11/1/201701563
11/2/201715633563
11/3/201735630
11/4/201700
11/5/201705000

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I'd like the bolded values to be 3563, because the next column has a 0 in it. 3563 should add to the 5000, so the Nanowrimo writer can get back to writing!

Link to image posted above ^^

Thanks!
 
Upvote 0
Maybe I am missing something and over-simplifying... does this give the desired result, in D5 and copied down?

=if(E4>0,E4,D4)

Maybe you only want the old word count to show up on the rows where the user enters a new word count, which could make use of a formula similar to the one you were trying... In that case you could put this in D5 and copy down:
=(E5<>0)*IF(E4<=0,LOOKUP(2,1/($E$4:$E4<>0),$E$4:$E4),E4)

Tai
 
Upvote 0
Would you please describe in words wat is required? Are the values under NEW WORD COUNT the desired results?
 
Upvote 0
Thank you so much, Tai, this fixes my problem!

Code:
[COLOR=#333333]=(E5<>0)*IF(E4<=0,LOOKUP(2,1/($E$4:$E4<>0),$E$4:$E4),E4)

[/COLOR]

I really appreciate you took the time to help me.

Have a great day!

- MASGaming




 
Upvote 0
Tai, your response is close to what I want, but I'd like the OLD WORD COUNT to always display the last written amount, not just when the writer puts in a NEW WORD COUNT. That way, the writer can wake up and see their previous words written.

Using the following as an example, I'd like the OLD WORD COUNT to always say 3563 no matter what is in the NEW WORD COUNT cell.

Thanks!



Example:
DATEOLD
WORD COUNT
NEW
WORD COUNT
11/1/201701563
11/2/201715633563
11/3/201735630
11/4/201700
11/5/201705000

<tbody>
</tbody>


Tai, your provided code is very close to what I want.

=(E5<>0)*IF(E4<=0,LOOKUP(2,1/($E$4:$E4<>0),$E$4:$E4),E4)
 
Last edited:
Upvote 0
For The NEW WORD COUNT, the writers just enter a number. There's no formula for the F column.

Here's the other formulas I have.

Column E: =(F6<>0)*IF(F5<=0,LOOKUP(2,1/($F$5:$F5<>0),$F$5:$F5),F5)
Column F: No formula

I'd like the OLD WORD COUNT to automatically populate with the previous day's NEW WORD COUNT so the writer can wake up and know what they wrote the previous day.

Tai's answer only puts in the OLD WORD COUNT once a NEW WORD COUNT is entered. Other than that, it works perfectly.

Thank you, Aladin!
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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