Eliminating "#VALUE!" for a formulas results
 

Hello, I am summing a column of this formula's results:
N9=IF(B9="r","-2",IF(B9=5,"-1",IF(B9="=","1","")))

When I use: N8=SUM(N9:N28) it only resulted in a zero, so I did according to
this other forum's answer: "It appears that your "numbers" are really text.
Changing the format of a text number cell to Number does not effect that a
"number" is really text. You must format the cell before entering the number.
Given that you have already entered the numbers, do this. In some empty
cell, enter a 1. Then copy that cell. Now select all the cells in which
you have your non-number numbers. Then click on Edit - Pastespecial and
select Multiply, then OK. That forces any entry that looks like a number to
a number. HTH Otto"

So my new formula is:
=(IF(B23="r","-2",IF(B23=5,"-1",IF(B23="=","1",""))))*1

Which works except if any cell in the B9-28 is blank, then it the results
cell produces "#VALUE!" and then also gives "#VALUE!" for the SUM.

Any advice?

Thank you in advance for your help!

Eliminating "#VALUE!" for a formula’s results
 

If you remove the double quotes from the numbers you won't have tp multiply
by 1

This revision will check for blank B23

=IF(B23="r",-2,IF(B23=5,-1,IF(B23="=",1,IF(B23="",""))))


Gord Dibben MS Excel MVP

On Tue, 26 Jan 2010 15:35:01 -0800, Jason
wrote:

Hello, I am summing a column of this formula's results:
N9=IF(B9="r","-2",IF(B9=5,"-1",IF(B9="=","1","")))

When I use: N8=SUM(N9:N28) it only resulted in a zero, so I did according to
this other forum's answer: "It appears that your "numbers" are really text.
Changing the format of a text number cell to Number does not effect that a
"number" is really text. You must format the cell before entering the number.
Given that you have already entered the numbers, do this. In some empty
cell, enter a 1. Then copy that cell. Now select all the cells in which
you have your non-number numbers. Then click on Edit - Pastespecial and
select Multiply, then OK. That forces any entry that looks like a number to
a number. HTH Otto"

So my new formula is:
=(IF(B23="r","-2",IF(B23=5,"-1",IF(B23="=","1",""))))*1

Which works except if any cell in the B9-28 is blank, then it the results
cell produces "#VALUE!" and then also gives "#VALUE!" for the SUM.

Any advice?

Thank you in advance for your help!