Nested Ifs

[DKRbella112316]

Hello - I have designed a spreadsheet to calculate cost savings based on the difference between old pricing for transaction volume of downloads and new pricing. Each month, a vendor reports total number of downloads generated from their software platform, which are billable. The downloads are billed according to a tiered pricing model; so, if downloads fall between a range of x (i.e. 501) and y (i.e. 1000), for example, then we will get charged a flat fee of z ($795). Under the old model of pricing, three separate divisions of our company were charged difference prices according to the old pricing model; under the new pricing model all divisions are charged the same price. In both old and new pricing models, there are up to 8 tiers of variable pricing.

I have structured the old pricing to be looked up via formula using nested Ifs. The initial file that I modified for this purpose was created in Excel 2003; my current version is Excel 2010 and I have renamed and saved the base file into a new file under the newer file format. That said, I have read that Excel 2010 allows for up to 64 (or something close) nested IFS - however, Excel is returning an error with my current formula (which only requires 21 nested IFS).

Can someone please review my formula and help me to correct? Also is there a possibility that since the base file was created in 2003 that I will not be able to save more than 7 nested IFS? Please help - a solution would make life so much simpler!!

=IF(AND(A8="B-L",Form2!C8<RawDataVar!$C$3),RawDataVar!$D$3,IF(AND(A8="B-L",RawDataVar!$B$4<Form2!C8,Form2!C8<RawDataVar!$C$4),RawDataVar!$D$4,IF(AND(A8="B-L",RawDataVar!$B$5<Form2!C$8,Form2!C8<RawDataVar!$C$5),RawDataVar!$D$5,IF(AND(A8="B-L",RawDataVar!$B$6<Form2!C$8,Form2!C8<RawDataVar!$C$6),RawDataVar!$D$6,IF(AND(A8="B-L",RawDataVar!$B$7<Form2!C$8,Form2!C8<RawDataVar!$C$7),RawDataVar!$D$7,IF(AND(A8="C-H",Form2!C8<RawDataVar!$C$8),RawDataVar!$D$8,IF(AND(A8="C-H",RawDataVar!$B$9<Form2!C8,Form2!C8<RawDataVar!$C$9),RawDataVar!$D$9,IF(AND(A8="C-H",RawDataVar!$B$10<Form2!C$8,Form2!C8<RawDataVar!$C$10),RawDataVar!$D$10,IF(AND(A8="C-H",RawDataVar!$B$11<Form2!C$8,Form2!C8<RawDataVar!$C$11),RawDataVar!$D$11,IF(AND(A8="C-H",RawDataVar!$B$12<Form2!C$8,Form2!C8<RawDataVar!$C$12),RawDataVar!$D$12,IF(AND(A8="C-H",RawDataVar!$B$13<Form2!C$8,Form2!C8<RawDataVar!$C$13),RawDataVar!$D$13,IF(AND(A8="C-H",RawDataVar!$B$14<Form2!C$8,Form2!C8<RawDataVar!$C$14),RawDataVar!$D$14,IF(AND(A8="C-H",RawDataVar!$B$15<Form2!C$8,Form2!C8<RawDataVar!$C$15),RawDataVar!$D$15,IF(AND(A8="C-H",RawDataVar!$B$16<Form2!C$8,Form2!C8<RawDataVar!$C$16),RawDataVar!$D$16),IF(AND(A8="Hydraulics",Form2!C8<RawDataVar!$C$17),RawDataVar!$D$17,IF(AND(A8="Hydraulics",RawDataVar!$B$18<Form2!C8,Form2!C8<RawDataVar!$C$18),RawDataVar!$D$18,IF(AND(A8="Hydraulics",RawDataVar!$B$19<Form2!C$8,Form2!C8<RawDataVar!$C$19),RawDataVar!$D$19,IF(AND(A8="Hydraulics",RawDataVar!$B$20<Form2!C$8,Form2!C8<RawDataVar!$C$20),RawDataVar!$D$20,IF(AND(A8="Hydraulics",RawDataVar!$B$21<Form2!C$8,Form2!C8<RawDataVar!$C$21),RawDataVar!$D$21,IF(AND(A8="Hydraulics",Form2!C8<RawDataVar!$C$22),RawDataVar!$D$22,IF(AND(A8="Hydraulics",RawDataVar!$B$23<Form2!C8,Form2!C8<RawDataVar!$C$23),RawDataVar!$D$23,)))))))))))))))))))))

 

[Joe4]

Re: Need Help with Formula - Nested Ifs

No, the instructions in the link I provided tell you to do the following:

check to see if you are using < or > in the formula.
If you are - add extra spaces around them (as done in the previous sentence).

If you put a space BEFORE and AFTER any < and > signs in your formula, it will post.