1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Functions - skip cells if they are blank

Discussion in 'Business Applications' started by mitchellmnr, Mar 8, 2013.

Thread Status:
Not open for further replies.
  1. mitchellmnr

    mitchellmnr Thread Starter

    Joined:
    Mar 8, 2013
    Messages:
    1
    Hello

    I have a spreadsheet with lets say the following values

    C1-9000
    C2-1000
    C3-{BLANK}
    C4-{BLANK}
    C5-{BLANK}
    C6-{BLANK}
    C7-{BLANK}
    C8-1100

    My cell uses the following formula to work out outstanding values.
    =IF(OR(C8<=0,C7<=0),"",(C8-C7))

    But now C7 is blank so my function will just be blank.

    I need a function that if C8 has a value it must get that value and subtract the next value, whether it is C7 or C2. In this case, C2.

    Please could someone give me a function to dynamcally decide what value to use.

    Thanks
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,986
    First Name:
    Wayne
    i think this will work
    But the numbers need to start at row 2
    and needs to be entered into cell C2 and then as an array- so control+shift+enter - so that {} are around
    =IF(C2="","",IF(OR(C2<=0,C1<=0),C2-INDIRECT("C"&(MAX(($C$1:C1<>"")*(ROW($C$1:C1))))),(C2-C1)))

    need to work out how to start

    basically
    (MAX(($C$1:C1<>"")*(ROW($C$1:C1))))
    will find the ROW number that is not blank
    http://www.cpearson.com/excel/LastValueInRowOrColumn.aspx
    we than add the Column C to the the ROW Number and use Indirect to use that in a formula
    INDIRECT("C"&

    and so we now have the value in the previous non blank cell
    but to stop it using the same row - i have changed the range to look on in previous rows - hence the C1 starting point
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1092358

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice