Thanks:  0
Likes:  0

# Thread: (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.

1. ## (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.

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:

2. ## Re: (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.

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

https://pasteboard.co/GShjPN7.png

3. ## Re: (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.

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:
`=IF(E5 <= 0, LOOKUP(2,1/(\$D\$4:\$D\$33<>0),\$D\$4:\$D\$33), E5)`
doesn't grab the last non-zero value.

Example:
 DATE OLD WORD COUNT NEW WORD COUNT 11/1/2017 0 1563 11/2/2017 1563 3563 11/3/2017 3563 0 11/4/2017 0 0 11/5/2017 0 5000

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!

4. ## Re: (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.

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

5. ## Re: (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.

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

6. ## Re: (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.

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

Code:
```=(E5<>0)*IF(E4<=0,LOOKUP(2,1/(\$E\$4:\$E4<>0),\$E\$4:\$E4),E4)

```
I really appreciate you took the time to help me.

Have a great day!

- MASGaming

8. ## Re: (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.

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:
 DATE OLD WORD COUNT NEW WORD COUNT 11/1/2017 0 1563 11/2/2017 1563 3563 11/3/2017 3563 0 11/4/2017 0 0 11/5/2017 0 5000

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)

9. ## Re: (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.

How do you get that 5000?

10. ## Re: (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.

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.