Subtraction question
 

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!

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.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Richard" skrev i en meddelelse
...
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!

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

This should work because MIN ignores the empty cells


--
Gary's Student


"Leo Heuser" wrote:

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.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Richard" skrev i en meddelelse
...
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!

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

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

"Leo Heuser" wrote:

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.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Richard" skrev i en meddelelse
...
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!

"Gary's Student" skrev i en
meddelelse ...
How about =max(C2:I2)-MIN(C2:I2)

This should work because MIN ignores the empty cells


--
Gary's Student

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

You're welcome, Richard, and thanks for the feedback :-)

LeoH

"Richard" skrev i en meddelelse
...
YES! Now that works. Thanks so much Leo!! Don't understand it but it works