| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula not working
Please help€¦ I have 3 cells with formulas in them
Cell L4 =IF(I4="< 5","4",IF(I4="5 - 199","3",IF(I4="200 - 500","2",IF(I4=" 500","1"," ")))) Cell M4 =IF(J4="< 500","4",IF(J4="500 - 19,999","3",IF(J4="20k - 50k","2",IF(J4=" 50k","1"," ")))) Cell N4 =IF(K4="< 5","4",IF(K4="5 - 199","3",IF(K4="200 - 500","2",IF(K4=" 500","1"," ")))) I need to be able to pull the smallest number that these calls fill in. I tried to use the Min formula but I think Excel only sees the 4, 3, 2&1 in my formula as text and not numbers. I tried formatting but that did not work ether. Can anyone give me a suggestion on haw to fix this???? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula not working
Remove the quotes from your numbers. Also, do you really want the final
false result to be a space? If you just want blank, then remove the space from the double-quote at the end of each formula. =IF(I4="< 5",4,IF(I4="5 - 199",3,IF(I4="200 - 500",2,IF(I4=" 500",1," ")))) Cell M4 =IF(J4="< 500",4,IF(J4="500 - 19,999",3,IF(J4="20k - 50k",2,IF(J4="50k",1," ")))) Cell N4 =IF(K4="< 5",4,IF(K4="5 - 199",3,IF(K4="200 - 500",2,IF(K4="500",1," ")))) HTH, Paul -- "Carolina Girl" wrote in message ... Please help. I have 3 cells with formulas in them Cell L4 =IF(I4="< 5","4",IF(I4="5 - 199","3",IF(I4="200 - 500","2",IF(I4=" 500","1"," ")))) Cell M4 =IF(J4="< 500","4",IF(J4="500 - 19,999","3",IF(J4="20k - 50k","2",IF(J4=" 50k","1"," ")))) Cell N4 =IF(K4="< 5","4",IF(K4="5 - 199","3",IF(K4="200 - 500","2",IF(K4=" 500","1"," ")))) I need to be able to pull the smallest number that these calls fill in. I tried to use the Min formula but I think Excel only sees the 4, 3, 2&1 in my formula as text and not numbers. I tried formatting but that did not work ether. Can anyone give me a suggestion on haw to fix this???? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula not working
Instead of returning "4", "3", "2", "1" or " " in your formulae, make
them just return the numbers 4, 3, 2, 1, or 0, and then your MIN formula will work. Alternatively, you could do it like this: =MIN(L4*1,M4*1,N4*1) the *1 will convert the text values to numbers. However, you will get #VALUE if your formula returns a space or formula blank. Hope this helps. Pete On May 16, 4:03*pm, Carolina Girl wrote: Please help… I have 3 cells with formulas in them Cell L4 =IF(I4="< 5","4",IF(I4="5 - 199","3",IF(I4="200 - 500","2",IF(I4=" 500","1"," ")))) Cell M4 =IF(J4="< 500","4",IF(J4="500 - 19,999","3",IF(J4="20k - 50k","2",IF(J4=" 50k","1"," ")))) Cell N4 =IF(K4="< 5","4",IF(K4="5 - 199","3",IF(K4="200 - 500","2",IF(K4=" 500","1"," ")))) I need to be able to pull the smallest number that these calls fill in. I tried to use the Min formula but I think Excel only sees the 4, 3, 2&1 in my formula as text and not numbers. I tried formatting but that did not work ether. Can anyone give me a suggestion on haw to fix this???? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula not working
Thanks for the help It worked GREAT....
"PCLIVE" wrote: Remove the quotes from your numbers. Also, do you really want the final false result to be a space? If you just want blank, then remove the space from the double-quote at the end of each formula. =IF(I4="< 5",4,IF(I4="5 - 199",3,IF(I4="200 - 500",2,IF(I4=" 500",1," ")))) Cell M4 =IF(J4="< 500",4,IF(J4="500 - 19,999",3,IF(J4="20k - 50k",2,IF(J4="50k",1," ")))) Cell N4 =IF(K4="< 5",4,IF(K4="5 - 199",3,IF(K4="200 - 500",2,IF(K4="500",1," ")))) HTH, Paul -- "Carolina Girl" wrote in message ... Please help. I have 3 cells with formulas in them Cell L4 =IF(I4="< 5","4",IF(I4="5 - 199","3",IF(I4="200 - 500","2",IF(I4=" 500","1"," ")))) Cell M4 =IF(J4="< 500","4",IF(J4="500 - 19,999","3",IF(J4="20k - 50k","2",IF(J4=" 50k","1"," ")))) Cell N4 =IF(K4="< 5","4",IF(K4="5 - 199","3",IF(K4="200 - 500","2",IF(K4=" 500","1"," ")))) I need to be able to pull the smallest number that these calls fill in. I tried to use the Min formula but I think Excel only sees the 4, 3, 2&1 in my formula as text and not numbers. I tried formatting but that did not work ether. Can anyone give me a suggestion on haw to fix this???? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula not working
Glad to help.
-- "Carolina Girl" wrote in message ... Thanks for the help It worked GREAT.... "PCLIVE" wrote: Remove the quotes from your numbers. Also, do you really want the final false result to be a space? If you just want blank, then remove the space from the double-quote at the end of each formula. =IF(I4="< 5",4,IF(I4="5 - 199",3,IF(I4="200 - 500",2,IF(I4=" 500",1," ")))) Cell M4 =IF(J4="< 500",4,IF(J4="500 - 19,999",3,IF(J4="20k - 50k",2,IF(J4="50k",1," ")))) Cell N4 =IF(K4="< 5",4,IF(K4="5 - 199",3,IF(K4="200 - 500",2,IF(K4="500",1," ")))) HTH, Paul -- "Carolina Girl" wrote in message ... Please help. I have 3 cells with formulas in them Cell L4 =IF(I4="< 5","4",IF(I4="5 - 199","3",IF(I4="200 - 500","2",IF(I4=" 500","1"," ")))) Cell M4 =IF(J4="< 500","4",IF(J4="500 - 19,999","3",IF(J4="20k - 50k","2",IF(J4=" 50k","1"," ")))) Cell N4 =IF(K4="< 5","4",IF(K4="5 - 199","3",IF(K4="200 - 500","2",IF(K4=" 500","1"," ")))) I need to be able to pull the smallest number that these calls fill in. I tried to use the Min formula but I think Excel only sees the 4, 3, 2&1 in my formula as text and not numbers. I tried formatting but that did not work ether. Can anyone give me a suggestion on haw to fix this???? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula not working | Excel Worksheet Functions | |||
| Formula only working sometimes | Excel Discussion (Misc queries) | |||
| Formula not working | Excel Worksheet Functions | |||
| Formula not working | Excel Worksheet Functions | |||
| Why isn't this formula working? | Excel Discussion (Misc queries) | |||
Linear Mode
