Subtraction

I have a very complex spreadsheet and I've encountered a problem I can't find a solution to..

Here is a snippet with my problem:

Please Login or Register  to view this content.

Excel has to check if cell A3 has a number in it, if true, then A20-A3
If A3 is blank, then check cell A2. If it has a number, then A20-A2
If A2 is blank, then A20-A1 (because A1 will always have a number)

Thanks for the help, but this didn't work in my spreadsheet.
It says there's an error in formula and highlights this part: 9.99999999999999E

I did make a mistake in my sample though, when labeling the headings..
My spreadsheet is horizontal, so it's A1, B1, C1, G1, H1, but I did modify the formula you gave to go horizontal.

And I actually simplified the sample, but in reality I have other columns in between the columns where actual data is.. maybe I should just give you the whole section of the spreadsheet.

Please Login or Register  to view this content.

Here is what I need to do: (O8+P8+R8+S8)-L8(if it's populated), if not check J8, if empty then subtract number in H8.

H8 will always have a number in it, but formula should only subtract one number.
columns I8, K8, M8 and N8 are just percentages and text info, they are not used by formula.

Thanks again.

Originally Posted by DonkeyOte

Assuming they are truly blank

A21: =A20-LOOKUP(9.99999999999999E+307,A1:A3)

Or alternatively if as implied in your sample A1:A3 are listed in Ascending order (ie A2 always > A1 if populated and so on)

A21: =A20-MAX(A1:A3)

Thanks again.

H,J,L are not always in ascending order, so I need to use the 2nd formula.. but I have tried it and I always get a 0 result. Any ideas?

By the way, I'm located in Russia but my laptop is from US and Excel is US-based... I use a semicolon as a delimiter.

I got it to work, had to fix one thing in the formula...

=SUM(O8:S8)-IF(SUM(L8);L8;IF(SUM(J8);J8;SUM(H8)))

=SUM(O8:R8)

because formula itself is in cell S8

Works great, thanks for your help!