Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I am trying to create an Excel formula unsucessfully.
example:
if cell D4 value is "0" then return "XXX'
if cell D4 value is "*" then return "ZZZ"
if cell D4 is blank then also return "ZZZ"
You have pretty well prototyped what you need to do,
If D4 = "0" then "XXX'
if D4 = "*" then "ZZZ"
if D4 = blank "ZZZ"Now just pop everything into the =IF formula which is
Defined as:=IF (logical_test, value_if_true, value_if_false)
=IF(D4=0,"xxx",IF(D4="*","zzz",IF(D4=" ","zzz",)))
A comma separates each part of the formula.
0 
OK thanks, it works sorta. I had left out some " marks.
If I enter "0" it returns xxx
If I enter "*" it returns ZZZ all good but
If I clear the cell with the delete key it returns xxx
If I clear the cell with the spacebar it returns ZZZ
obviously there must be a difference and a correct way to handle this.Thanks again,
Ken
0
If I clear the cell with the delete key it returns xxx
That because Excel thinks it now contains a zero.......If I clear the cell with the spacebar it returns ZZZ
That because the "space" is a character........
In the formula D4 = " " there is a space between the quotes.For more on "blank" Cells in excel:
http://support.microsoft.com/kb/214244
0
@ Mike
I thought you liked 'em "short and sweet"?
=IF(D4=0,"xxx",IF(OR(D4="*",D4=" "),"zzz"))
7 characters less than your suggestion. <g>
0
@ kvw
In the future, please try to use a relevant subject line for your posts.
Something like 'IF statement question" would have been appropriate for this thread.
It helps keep the forum a little more organized and a little easier to search.
Thanks!
0
I was just showing how he had it already prototyped
in his question and just illustrated that all he had to do was "plug & play".I did not think that introducing an OR function into the mix would be helpful at that time, especially if he was having problems understanding an IF.
But, now that he seems to "grok" IF perhaps he can graduate to something a bit more elegant and "shorter"....
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
