Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding (and eliminating) hidden "space" character | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
pivot tables, eliminating "0" values | Excel Worksheet Functions | |||
Eliminating "tails" on line charts | Charts and Charting in Excel | |||
Eliminating the quote "This document was sent to the printer" | Excel Discussion (Misc queries) |