Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
In F8, I have a formula that results in the value "0" (zero). I have the
cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
It could be that your formula is actually returning a TEXT character rather
than a numerical ZERO. Try a test by re-typing a simple formula in F8 that will return zero, like =a1 if A1 is empty. hth Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
That was it. I had =IF(E8="0","0",IF(E8<0,E8+1,IF(E81,E8-1,E8)))
in F8. When I removed the quotations and changed the formula to =IF(E8="0",0,IF(E8<0,E8+1,IF(E81,E8-1,E8))) everything ran smoothly. Thanks for your help. "CLR" wrote: It could be that your formula is actually returning a TEXT character rather than a numerical ZERO. Try a test by re-typing a simple formula in F8 that will return zero, like =a1 if A1 is empty. hth Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
Not only that, but I think your formula in H8 should become:
=IF(F8=2,1,IF(F8=1,2, IF(F8=0,3,IF(F8=-1,4,IF(F8<-2,5,"N/A"))))) unless you really want those numbers 1 to 5 to be text values. Hope this helps. Pete On Jan 29, 7:01 pm, CLR wrote: It could be that your formula is actually returning a TEXT character rather than a numerical ZERO. Try a test by re-typing a simple formula in F8 that will return zero, like =a1 if A1 is empty. hth Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8?- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
Looks like your formula actually produces a text value that "looks" like a
number. For example, '0 will display as 0 but left aligned rather than right aligned. Note that all the results from the formula in cell H8 will also be text rather than numeric because you have put the result in quotes. If you want numeric output from the formula in H8, it needs to be: =IF(F8=2,1,IF(F8=1,2, IF(F8=0,3,IF(F8=-1,4,IF(F8<-2,5,"N/A"))))) That might be what you are doing in cell F8 but, as you've not posted the formula, that's just an educated guess. Regards Trevor "StephanieH" wrote in message ... In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
Glad you got it sorted..............thanks for the feedback.
Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: That was it. I had =IF(E8="0","0",IF(E8<0,E8+1,IF(E81,E8-1,E8))) in F8. When I removed the quotations and changed the formula to =IF(E8="0",0,IF(E8<0,E8+1,IF(E81,E8-1,E8))) everything ran smoothly. Thanks for your help. "CLR" wrote: It could be that your formula is actually returning a TEXT character rather than a numerical ZERO. Try a test by re-typing a simple formula in F8 that will return zero, like =a1 if A1 is empty. hth Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
What's the formula look like in F8?
If it's similar to this one: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) The problem is that when enclose numbers in quotes, Excel evaluates them as TEXT. So: TEXT "0" =2 is TRUE, thus the result being TEXT "1". Try removing the quotes from around your numbers in both formulas: =IF(F8=2,1,IF(F8=1,2, IF(F8=0,3,IF(F8=-1,4,IF(F8<-2,5,"N/A"))))) Here's another way to do that: =LOOKUP(F8,{-1000000,-2,-1,0,1,2},{"N/A",5,4,3,2,1}) Biff "StephanieH" wrote in message ... In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
You need to sort out the difference between numbers and text strings.
Your "0" is a text string, and that evaluates to greater than the number 2, hence the answer is the text string "1", from the first test in your formula. If you want numbers, leave out the quote marks. If in F8 you put 0, rather than "0", you'll get "3", but of course that too is a text string, not a number. -- David Biddulph "StephanieH" wrote in message ... In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
Good point. I hadn't gotten far enough to work with those results yet. I
think you just saved me an additional 1/2 hour ; - ) Thanks Pete. "Pete_UK" wrote: Not only that, but I think your formula in H8 should become: =IF(F8=2,1,IF(F8=1,2, IF(F8=0,3,IF(F8=-1,4,IF(F8<-2,5,"N/A"))))) unless you really want those numbers 1 to 5 to be text values. Hope this helps. Pete On Jan 29, 7:01 pm, CLR wrote: It could be that your formula is actually returning a TEXT character rather than a numerical ZERO. Try a test by re-typing a simple formula in F8 that will return zero, like =a1 if A1 is empty. hth Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8?- Hide quoted text -- Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero as a value
Thanks for the feedback, Stephanie - glad to help.
Pete On Jan 29, 7:27 pm, StephanieH wrote: Good point. I hadn't gotten far enough to work with those results yet. I think you just saved me an additional 1/2 hour ; - ) Thanks Pete. "Pete_UK" wrote: Not only that, but I think your formula in H8 should become: =IF(F8=2,1,IF(F8=1,2, IF(F8=0,3,IF(F8=-1,4,IF(F8<-2,5,"N/A"))))) unless you really want those numbers 1 to 5 to be text values. Hope this helps. Pete On Jan 29, 7:01 pm, CLR wrote: It could be that your formula is actually returning a TEXT character rather than a numerical ZERO. Try a test by re-typing a simple formula in F8 that will return zero, like =a1 if A1 is empty. hth Vaya con Dios, Chuck, CABGx3 "StephanieH" wrote: In F8, I have a formula that results in the value "0" (zero). I have the cell formatted as a number. In H8, I have the following formula: =IF(F8=2,"1",IF(F8=1,"2", IF(F8=0,"3",IF(F8=-1,"4",IF(F8<-2,"5","N/A"))))) Given that my result in F8 is 0, I would expect the expression to evaluate to 3. However, it returns 1. When I type over the result of the formula and enter 0 directly into the cell, it does returns the correct answer of 3. In each of my tests, when F8 results in anything other than 0, H8 seems to work fine. How do I get Excel to recognize the value 0 for F8 in cell H8?- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|