"IF" Function, excel not recognising my numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I just made a simple formula for excel to find a value of a number that has a
bracket attached, then if the value is greater than 50, it will replace the
cells value with 50. This is what I put in the function

IF LEFT(W1,LEN(W1)-1)>"50" = TRUE
50
LEFT(W1,LEN(W1)-1)

12.0603} I get 12.0603

7.007} I get 50 (Why?)
6.4142} I get 50 ?
26.7647} I get 26.7647 correct
34.2744}I get 34.2744 correct
2.0283} I get 2.0283 correct
27.5844} i get 27.5844 correct
76.44} I get 50 correct
73.5583} I get 50 correct
etc etc
Can anyone see why it would be doing this?

Even if i take out all the numers from the bracket, and use
IF A1>"50" 50,A1
I still get same error!

Any help appreciated!
 
Since you have "50" in quotes, XL is doing a string comparison, rather
than a numeric comparison. Since the "7" in "7.007" has a higher ASCII
value than the "5" in "50", the first comparison will be true.

You can remove the quotes, and convert the string returned by LEFT() to
a number using two - signs (one for the conversion, one to make the
negative result positive):

=IF(--LEFT(A1,LEN(A1)-1)>50,50,LEFT(A1,LEN(A1)-1))

Alternatively, the MIN() function will do the coercion for you:

=MIN(50,LEFT(A1,LEN(A1)-1))
 
Back
Top