If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"IF" Function, excel not recognising my numbers
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! |
#2
|
|||
|
|||
=IF(--LEFT(A1,LEN(A1)-1)50,50,--LEFT(A1,LEN(A1)-1))
Hope this helps! In article , "Aqua" wrote: 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! |
#3
|
|||
|
|||
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)) In article , "Aqua" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can I create a function in Excel? | Jose | General Discussion | 3 | July 22nd, 2004 07:36 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |
Word or excel function to create increasing numbers? | dmdivers | General Discussions | 0 | June 4th, 2004 01:41 PM |
looking for Excel Function Reference book from 1992? | Matt | Worksheet Functions | 2 | October 29th, 2003 08:04 PM |