Formula for last entry in a column not zero

Discussion in 'Mac Basics and Help' started by JWmacuser, Aug 6, 2010.

  1. JWmacuser macrumors newbie

    Joined:
    Aug 6, 2010
    #1
    Working on a spreadsheet in NUMBERS. Want to write a logic statement/formula to pick up the most recent non-zero entry in a column. Something like "IF C31=0, then C30" but then if C30 equals zero go to C29, then C28 if C29 is zero and so on.
     
  2. xUKHCx Administrator emeritus

    xUKHCx

    Joined:
    Jan 15, 2006
    Location:
    The Kop
    #2
    Ideally some sort of context of how you would want to use this would be best as there may be a more suitable method for doing this but the following formula reports the row position in a range where 0 first appears.

    The first 0 is the search term, the B refers to column B, the final 0 refers to which value it picks up.

    So in the following range

    1
    2
    3
    0
    9
    2
    2
    1
    0

    The formula reports 4. Note it ignores the later 0.

    If you want it to pick up the last 0 then change the formula to:

    On the above range this formula reports 9.
     
  3. JWmacuser thread starter macrumors newbie

    Joined:
    Aug 6, 2010
    #3
    context is: At the end of every day I post a sold units number in column F (rows 1 through 31 for each day). In F32, I would like the most recent sold units amount to show.

    so in column F the entries are
    row 1 value = 4
    row 2 value = 5
    row 3 value = 7
    row 4 value = 9
    row 5 value = 14
    row 6 value = 0
    .
    .
    .
    .
    through
    row 31 value = 0

    I would like row 32 to pick up row 5 value of 14 because it is the latest non-zero entry.
     
  4. xUKHCx Administrator emeritus

    xUKHCx

    Joined:
    Jan 15, 2006
    Location:
    The Kop
    #4
    Give this a go:

    If there aren't any "0"'s then it will just give a red triangle. I had a little look at the match function and it doesn't seem possible to give it parameters to output if it can't match.

    If you want error checking I'll have another look in the morning.
     
  5. JWmacuser thread starter macrumors newbie

    Joined:
    Aug 6, 2010

Share This Page