# Excel Functions - skip cells if they are blank

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

Not open for further replies.

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. ### etafModerator

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