Inequalities and ranges

[dchatrie]

I am trying to write a formula, which would involve choosing between a range and then return the corresponding scores. Here is what i did: If(AND(G1="Direct",2500>=G2>=50000),1,""). But it is not working. Kindly assist

 

[oldchippy]

Hi,


Is this what you are trying to do?


=IF(AND(G1="Direct",G2>=2500,G2<=50000),1,"")

 

[dchatrie]

Yes thank you

 

[oldchippy]

You're welcome, thanks for the feedback

 

[dchatrie]

I have completed the formula and it is functional, however I was wondering if anyone know a shorter way it could be written using some other function/formulas. Here is the formula:

IF(AND(F4="Regular",G4>=2500,G4<=50000),1,IF(AND(F4="Regular",G4>=50001,G4<=249000),2,IF(AND(F4="Regular",G4>=250000),3,IF(AND(F4="Direct",G4>=25000,G4<=50000),1,IF(AND(F4="Direct",G4>=50001,G4<=99000),2,IF(AND(F4="Direct",G4>=100000,G4<=249999),3,IF(AND(F4="Direct",G4>=250000,G4<=499999),5,IF(AND(F4="Direct",G4>=500000,G4<=999999),7,IF(AND(F4="Direct",G4>=1000000),10,IF(AND(F4="Term",G4>=50000,G4<=249999),3,IF(AND(F4="Term",G4>=250000,G4<=499999),5,IF(AND(F4="Term",G4>=500000),7,"")))))))))))).


Thank you

 

[Luke M]

dchatrie,


It might be easier to setup some lookup table in your worksheet so things are easier to change later, but as a first pass, your formula can be reduced to this:

=IF(F4="Regular",SUMPRODUCT((G4>={2500,50001,250000})*1),IF(F4="Direct",LOOKUP(G4,{0,25000,50001,100000,250000,500000,1000000},{0,1,2,3,5,7,10}),IF(F4="Term",LOOKUP(G4,{0,50000,250000,500000},{0,3,5,7}),"")))


For the Direct and Term conditions, since you were skipping integers as outputs, I used the LOOKUP function. For the Regular outputs, since it's just incrementing by one, I used the method described here:

http://chandoo.org/wp/2012/02/16/formula-forensics-012/

 

[dchatrie]

Thank you Luke, your response was great.