Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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. #1
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question (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:




    Thanks for all your help!

  2. #2
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    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:
    =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. #4
    Board Regular
    Join Date
    Mar 2007
    Location
    Chicago Area
    Posts
    2,614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,412
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default 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?
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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





  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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, Aladin, for replying. Tai has fixed my issue.

    I really appreciate your response.

  8. #8
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    Last edited by MASGaming; Nov 12th, 2017 at 11:10 AM. Reason: Need to add some code

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,412
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default 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?
    Last edited by Aladin Akyurek; Nov 12th, 2017 at 02:19 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Thank you, Aladin!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •