Formula not working

[Carolina Girl]

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????

 

[PCLIVE]

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

 

[Pete_UK]

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

 

[Carolina Girl]

Thanks for the help It worked GREAT....

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

 

[PCLIVE]

Glad to help.

--

Thanks for the help It worked GREAT....