Subtraction question

[Guest]

Subtracting cells C2 thru I2
Example: If C2 had 3436, D2 had 3395, E2 had 3376, then I need the answer in
cell J2 to be 60 EVEN IF THE CELLS F2 THRU I2 ARE BLANK.
This formula works fine if I have numbers in ALL cells (C2:I2)
"=MAX(C2:H2)-I2" but when there are blank cell it doesn't count right.
Thanks in advance!

 

[Leo Heuser]

Richard

If I have understood your question correctly, this formula entered in J2
will do the trick.

=MAX(OFFSET(C2,,,,COUNTA(C2:I2)-1))-OFFSET(C2,,COUNTA(C2:I2)-1)

You can't have for instance a number in C2, D2 blank and a number in E2.
The blanks are supposed to come between the last filled cell in C2:I2 and
J2.

 

[Guest]

How about =max(C2:I2)-MIN(C2:I2)

This should work because MIN ignores the empty cells

 

[Leo Heuser]

If the situation

You can't have for instance a number in C2, D2 blank and a number in E2.

arises, enter 0 (zero) in D2

LeoH

 

[Guest]

YES! Now that works. Thanks so much Leo!! Don't understand it but it works

 

[Leo Heuser]

How about =max(C2:I2)-MIN(C2:I2)

This should work because MIN ignores the empty cells

The expression MAX(C2:H2) in the OP's formula indicates, that the
numbers from C2 to H2 are not always in descending order,
else he would have used C2 instead of MAX(C2:H2), I guess.

LeoH

 

[Leo Heuser]

You're welcome, Richard, and thanks for the feedback

LeoH