(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]

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:

Thanks for all your help!

 

[MASGaming]

OP:
Link to image, in case it isn't working

https://pasteboard.co/GShjPN7.png

 

[MASGaming]

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

[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:
[TABLE="width: 421"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]OLD
WORD COUNT[/TD]
[TD]NEW
WORD COUNT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]0[/TD]
[TD]1563[/TD]
[/TR]
[TR]
[TD]11/2/2017[/TD]
[TD]1563[/TD]
[TD]3563[/TD]
[/TR]
[TR]
[TD]11/3/2017[/TD]
[TD]3563[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11/4/2017[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11/5/2017[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[taigovinda]

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

 

[Aladin Akyurek]

Would you please describe in words wat is required? Are the values under NEW WORD COUNT the desired results?

 

[MASGaming]

Thank you so much, Tai, this fixes my problem!

[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

 

[MASGaming]

Thank you, Aladin, for replying. Tai has fixed my issue.

I really appreciate your response.

 

[MASGaming]

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:
[TABLE="class: cms_table, width: 421"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]OLD
WORD COUNT[/TD]
[TD]NEW
WORD COUNT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]0[/TD]
[TD]1563[/TD]
[/TR]
[TR]
[TD]11/2/2017[/TD]
[TD]1563[/TD]
[TD]3563[/TD]
[/TR]
[TR]
[TD]11/3/2017[/TD]
[TD]3563[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11/4/2017[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11/5/2017[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[/TR]
</tbody>[/TABLE]


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)

 

[Aladin Akyurek]

How do you get that 5000?

 

[MASGaming]

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!