IF statement
Posted by Rob Moone on October 25, 2001 2:37 AM
If have a problem with an IF statement. I want to exclude all numbers above 8000 but the numbers 8761 and 8951 should be included. Any suggestions?
Posted by Aladin Akyurek on October 25, 2001 3:05 AM
What does or is supposed to compute the formula that must contain this IF-part? Care to post the formula and/or elaborate on what you want to compute?
Aladin
Posted by suf on October 25, 2001 3:13 AM
This works:
=if(and(a1<>8761,a1<>8951,a1>8000)," ",a1)
Three conditions: not equal to 8761, not equal to 8951, greater than 8000.
Quotations to leave blank if greater than 8000, otherwise to show either 8761 or 8951.
Posted by Aladin Akyurek on October 25, 2001 3:44 AM
Your formula would return a 0 when A1 is blank, i.e., empty. Moreover, it "confounds" space with blank (" " versus ""). An alternative would be:
=IF(A1,IF(OR(A1={8761,8951},A1<=8000),A1,""),"")
However, it's possible that Rob is trying to compute something where he needs to exclude numbers beyond 8000 excepting 8761 and 8951.
Regards,
Aladin
Posted by Rob Moone on October 25, 2001 4:33 AM
:Here is the complete formula. We have what we call projectnumbers (4-digit e.a. 7642 and 8883)for all numbers under 8000 (except those ending with 3) excel converts the currency from dutch guilders to the new euro currency but for 8761 and 8951 excel should also do this convertion.
=IF(K$1="";0;IF($J$2>8000;0;IF(OR($J$2=5253;$J$2=7043;$J$2=7643;$J$2=7723;$J$2=7373;$J$2=7943;$J$2=6553;$J$2=6543;$J$2=6533;$J$2=6293;$J$2=6273;$J$2=6263;$J$2=6193;$J$2=6053;$J$2=4253);0;IF($J$2=5403;0;IF($J$2=5783;0;IF($J$2=5593;0;IF($J$2=6843;0;IF($J$2=6913;0;(N31/1,06)+N40))))))))/2,20371
Posted by Aladin Akyurek on October 25, 2001 5:24 AM
Rob,
Try:
=IF(LEN(K$1),IF(OR(AND(RIGHT($J$2)+0<>3,$J$2<=8000),OR($J$2={8761,8951})),(N31/1.06)/2.20371,0),0)
Note. Replace the list separator "," with ";" except maybe within {} and "." with "," in numbers. I kept $-signs in cell addresses, although I'm not sure if you need them.
Aladin
