conditional formulas

[mcaicedo]

Hi All,
If anyone could help me, it would be appreciate. I have three columns with
numbers and text (see below).The result is in column B and C but I have not
be able to find a formula to show me a message that is wrong. If it is not
within the range the cell should be blank, otherwise the cell should be shown
a message “wrongâ€. I am working in excel 2003

Thanks in advance

Column A Column B Colun C
data number text
100058-0020 =IF($A7<=7999,$A7,"") =IF($A7>=8000,$A7,"")
CC3101 wrong
100069-0020 100069-0020
100072-0020 100072-0020
7040 7040
100312-0010 100312-0010
100313 100313
72101 wrong
100316-0010 100316-0010
100321-0010 100321-0010
7002 7002
7012 7012
X wrong

 

[Jacob Skaria]

Is this what you are looking for,,,

In B2
=IF($A2<=7999,$A2,"")

In C2
=IF(AND(ISNUMBER($A2),A2>=8000),$A2,"wrong")

If this post helps click Yes

 

[mcaicedo]

It does not work in column C. It shows a message "wrong" and it should be
"100058-0020" as below. Thanks anyway for your help --
mcaicedo

 

[mcaicedo]

Hi Jacob,
I have changed the formula to: =IF(AND(ISNUMBER($A1),$A1<=7999),"",$A1) but
now row 2 shows CC3101 and it should be "wrong", Rows 8 and 13 should be
“wrong†as well --
mcaicedo

 

[Jacob Skaria]

Sorry, I am a bit confused on what you are looking for.

ISNUMBER() function returns true or false
ISTEXT() validates a text string and returns true or false

You can have multiple conditions with AND() to validate

If you need help post back with sample data and expected
answers in ColB and ColC..The current example seems to be messed up with the
formulas....

If this post helps click Yes

 

[mcaicedo]

Hi Jacob,
Thanks for your reply. Below is what I am expecting:

Column A Column B Column C
100058-0020 100058-0020
CC3101 wrong
100069-0020 100069-0020
100072-0020 100072-0020
7040 7040
100312-0010 100312-0010
100313 100313
72101 wrong
100316-0010 100316-0010
100321-0010 100321-0010
7002 7002
7012 7012
X wrong

Many thanks,--
mcaicedo