IF function

  • Thread starter Thread starter Po
  • Start date Start date
P

Po

I have a range of rates , i.e import between $0 and $ 10 cost x$, between
$11.01 and 20 cost y$ , and so on.

I am using the following
=IF(AND(O4>0,O4<=25),$D$1,IF(AND(O4>25.01,O4<=50),$D$2,IF(AND(O4>51,O4<=100),$D$3,IF(AND(O4>101,O4<=250),$D$4,IF(AND(O4>251,O4<=500),$D$5,IF(AND(O4>501,O4<=1000),$D$6,IF(AND(O4>1001,O4<=2000),$D$7,"-")))))))

But I cannot go beyond 7 IFs and I have 26 "import between $0 and $ 10 cost
x$"

Any suggestions ??
 
.. cannot go beyond 7 IFs and
I have 26 "import between $0 and $ 10 cost x$"
Click to expand...

Taking it at face value .. here's one way to get it done neater

Assume your limits* (as posted) are listed in B1:C4

0 25
25.01 50
51 100
101 250

you could place this in say P4, array-entered
(press CTRL+SHIFT+ENTER to confirm the formula):
=INDEX(D1:D4,MATCH(1,(O4>B1:B4)*(O4<=C1:C4),0))
Adapt/Extend the ranges to suit your actuals

*There are inconsistencies in your limits as indicated in your posted
formula.
These should be rectified.

---
 
Back
Top