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
|
|||
|
|||
Using multiple IF statements
I have to create a column that shows traffic movement data.
I'm not really used to Excel and this is the formula I have come up with thus far: =(IF(AND(G230,H230),"oops","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"YAY","HMMM")) Each part on it's own works but when I put them together it gives an output of '#VALUE' I've tried some different ways of doing this but the output is always either '#VALUE' or '#REF' Any assistance would be much appreciated Thanku |
#2
|
|||
|
|||
Using multiple IF statements
David
Just change the last "" in my formula to "HMMM" Andy "David" wrote in message ... I have to create a column that shows traffic movement data. I'm not really used to Excel and this is the formula I have come up with thus far: =(IF(AND(G230,H230),"oops","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"YAY","HMMM")) Each part on it's own works but when I put them together it gives an output of '#VALUE' I've tried some different ways of doing this but the output is always either '#VALUE' or '#REF' Any assistance would be much appreciated Thanku |
#3
|
|||
|
|||
Using multiple IF statements
Perhaps this may help
=IF(AND(G230,H230),"oops", IF(AND(G230,H230),"MAYBE",IF(AND(G230,H230),"M AYBE",IF(AND(G230,H230), "YAY","HMMM")))) Breaking it down if g23 & h230 then 'oops' else if g230&h230 then 'maybe' else if g230 & h230 then 'maybe' else is g230 & h230 then 'yay otherwise 'hmmm' Of course I hope you relise you're not testing for 0 (zero) values .. you may need to use = or = Hope this helps Ken "Andy B" wrote in message ... David Just change the last "" in my formula to "HMMM" Andy "David" wrote in message ... I have to create a column that shows traffic movement data. I'm not really used to Excel and this is the formula I have come up with thus far: =(IF(AND(G230,H230),"oops","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"YAY","HMMM")) Each part on it's own works but when I put them together it gives an output of '#VALUE' I've tried some different ways of doing this but the output is always either '#VALUE' or '#REF' Any assistance would be much appreciated Thanku |
#4
|
|||
|
|||
Using multiple IF statements
"David" wrote...
I'm not really used to Excel and this is the formula I have come up with thus far: =(IF(AND(G230,H230),"oops","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"YAY","HMMM")) Each part on it's own works but when I put them together it gives an output of '#VALUE' That's because it doesn't combine into something Excel can use. First, you have excessive parentheses. Removing them gives =IF(AND(G230,H230),"oops",""), IF(AND(G230,H230),"MAYBE",""), IF(AND(G230,H230),"MAYBE",""), IF(AND(G230,H230),"YAY","HMMM") which shows that you're trying to combine various text-valued results with commas. That doesn't work. You could combine them with the concatenation operator, &, as =IF(AND(G230,H230),"oops","")& IF(AND(G230,H230),"MAYBE","")& IF(AND(G230,H230),"MAYBE","")& IF(AND(G230,H230),"YAY","HMMM") but better still would be to remove some of the redundancy. You have two state variables, G23 and H23, and 9 rather than 4 combined states (either equal to 0). So, =IF(OR(G23=0,H23=0),"HMMM", CHOOSE(2+(SIGN(G23)+SIGN(H23))/2,"YAY","MAYBE","oops")) -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#5
|
|||
|
|||
Using multiple IF statements
-----Original Message----- I have to create a column that shows traffic movement data. I'm not really used to Excel and this is the formula I have come up with thus far: =(IF(AND(G230,H230),"oops","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"MAYBE","")), (IF(AND(G230,H230),"YAY","HMMM")) Each part on it's own works but when I put them together it gives an output of '#VALUE' I've tried some different ways of doing this but the output is always either '#VALUE' or '#REF' Any assistance would be much appreciated Thanku . Each if has to have a true or false statement so your second if function has to be either the true or false of the first if function. eg =(IF(AND(G230,H230),"oops",IF(AND(G230,H230)," MAYBE", IF(AND(G230,H230),"MAYBE2",(IF(AND(G230,H230), "YAY", "HMMM"))) |
Thread Tools | |
Display Modes | |
|
|