|
conditional formulas
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 -- mcaicedo |
conditional formulas
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 --------------- Jacob Skaria "mcaicedo" wrote: 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 -- mcaicedo |
conditional formulas
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "mcaicedo" wrote: 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 -- mcaicedo |
conditional formulas
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 "mcaicedo" wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "mcaicedo" wrote: 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 -- mcaicedo |
conditional formulas
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 --------------- Jacob Skaria "mcaicedo" wrote: 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 "mcaicedo" wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "mcaicedo" wrote: 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 -- mcaicedo |
conditional formulas
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "mcaicedo" wrote: 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 "mcaicedo" wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "mcaicedo" wrote: 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 -- mcaicedo |
| All times are GMT +1. The time now is 11:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com