+ Reply to Thread
Results 1 to 6 of 6

Check if Blank, If so, Check the Next Cell

  1. #1
    Registered User
    Join Date
    11-29-2011
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Check if Blank, If so, Check the Next Cell

    I need to write a tricky formula/code to do a slightly more advanced form of subtraction. Here's the scenario:

    I have a long list of values, one value per day. I need to make a separate column that takes the value for the current day and subtracts the value from the day before. This is the easy part.

    The difficult part is that not every day has a value (therefore the cell is blank). If I could write formula/code that checks if the value is blank and if so checks the cell above if it is blank until it finds the next value to use in the subtraction. If the cell itself is "~" then it returns "Null". Here's an example to help clarify

    Value----------------Subtraction
    --4
    --7------------------------3
    --12-----------------------5
    --15-----------------------3------.
    ---~----------------------Null-----l----- Skips the null and subtracts the 15 from 20, resulting in 5
    --20-----------------------5------'
    --22-----------------------2------.
    ---~----------------------Null-----l
    ---~----------------------Null-----l-----Skips the 3 nulls and subtracts the 22 from 30, resulting in 8
    ---~----------------------Null-----l
    --30-----------------------8------'

    Hopefully there is a way around this. Any help is appreciated! Thanks a lot for reading
    Last edited by Watterboy; 12-08-2011 at 11:34 AM.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Check if Blank, If so, Check the Next Cell

    Watterboy,

    Attached is an exammple workbook based on the criteria you provided. In Column A is the Value, Column B is the subtraction. Row 1 is headers, so data starts on row 2. Because there is no subtraction happening for the first value, cell B2 is blank. The subtraction formula starts in cell B3:
    =IF(OR(A3={"","~"}),"Null",A3-LOOKUP(2,1/(ISNUMBER($A$2:A2)),$A$2:A2))

    And is copied down. Is something like that what you were looking for?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    11-29-2011
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Check if Blank, If so, Check the Next Cell

    Quote Originally Posted by tigeravatar View Post
    Watterboy,

    Attached is an exammple workbook based on the criteria you provided. In Column A is the Value, Column B is the subtraction. Row 1 is headers, so data starts on row 2. Because there is no subtraction happening for the first value, cell B2 is blank. The subtraction formula starts in cell B3:
    =IF(OR(A3={"","~"}),"Null",A3-LOOKUP(2,1/(ISNUMBER($A$2:A2)),$A$2:A2))

    And is copied down. Is something like that what you were looking for?
    This seems to be exactly what im looking for! That is some awesome code work. Could you possibly explain how you used the LOOKUP command? I understand everything else that you did in the code, but Ive never used the LOOKUP command and I'm not understanding how it works or how you used it.

    Your method is far more elegant and effective than how I did it. I used a series of IF statements that performed checks. It was only effective up to 5 cells.

    Thanks again!
    Last edited by Watterboy; 12-08-2011 at 12:29 PM.

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Check if Blank, If so, Check the Next Cell

    tigeravatar,

    Good one. a small improvement. Since he is looking for a last number, you can use LOOKUP like, LOOKUP(9E300,A$2:A2)

    =IF(OR(A3={"","~"}),"Null",A3-LOOKUP(9E300,A$2:A2))

    Watterboy,

    LOOKUP will look for a last number/text (dependent on lookup_value) <=lookup_value. Here you are looking for a number. 9E300 is a scientific notation of a large number. 9 with 300 zeros, which is a really really big number. So always looking for a value <=9E300.

    At B3, this will look for a last number in A$2:A2, which is A2 = 2
    At A7, this will look for a last number in A$2:A6, which is A5 = 15
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    11-29-2011
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Check if Blank, If so, Check the Next Cell

    How does the command know to begin its search from the botton of the array rather than the top though?
    How do you tell the command where to start searching? That is the key to making it work.

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Check if Blank, If so, Check the Next Cell

    A2 to down starts values.
    B3 to down need to show the subtraction.

    In B3, if you use LOOKUP(9E300,A$2:A2) this will look for a number in A2. when you copy down, say at A11 formula will become =LOOKUP(9E300,A$2:A10) (you have blocked row # 2 with $, A$2. When you copy down this will stay with row# 2). This will look for a LAST number in A2:A10. On the example it is on A8.

    In C3, enter this formula & copy down.

    =LOOKUP(9E300,A$2:A2)

    you can see you always get the last number.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1