Formula not working

C

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

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
 
P

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
 
C

Carolina Girl

Thanks for the help It worked GREAT....


PCLIVE said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Leave final cell blank 2
IF functions 2
if function error 3
Using Formulas 3
nested "If" fuction 4
TODAY() issues 6
help with input box 1
If results for multiple cells 1

Top