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,)))))))))))))))))))))

 

[Marcelo Branco]

Re: Need Help with Formula - Nested Ifs

Welcome to Mr Excel

To improve your chances to get help try to provide a small data sample, including the tiered price model, along with expected results.

To post a data sample take a look at Section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[DKRbella112316]

Re: Need Help with Formula - Nested Ifs

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,)))))))))))))))))))))< font="">

</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

It appears that your formula is getting chopped off.
See this to get it to post correctly: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[DKRbella112316]

Re: Need Help with Formula - Nested Ifs

To clarify above post, I have written a macro which pulls transaction data details by month from the 'Data' worksheet within the workbook and pastes this data into a second worksheet ('Form') which is a standard form for calculating savings based on formulas that are programmed within the cells. The formula which I am currently having the greatest challenge with is the nested IFS, which uses logic to compare that month's transaction volume (by Division) against a variable pricing table in a third worksheet called 'VariablePricing'. For example,

In 'Form' worksheet, transaction data will be pasted into the following cells: Col A = Customer Division (rows 8+, depending on # of customer divisions), Col B = Total Transactions (rows 8+; used for calculating old pricing), Col C = CAD Billable (rows 8+ used for calculating new pricing), Col G = Variable Fee Old Pricing (rows 8+, written with provided nested IF formula with intent to reference Col A - customer division to lookup old pricing in 'Variable Pricing' spreadsheet. An example of one division's variable pricing table is below:

'Variable Pricing' spreadsheet - pricing table for B-L Division:

Col A = Division (i.e. A3 = B-L)
Col B = Min transaction range (i.e. B3 = 501)
Col C = Max transaction range (i.e. C3 = 1000)
Col D = Monthly Fee (i.e. D3 = $750)


Depending on the Division referenced in cells A8+ in 'Form' worksheet, the nested IF formula below should return the correct variable fees. However, I the formula is returning an error, which is where I need help.

 

[DKRbella112316]

Re: Need Help with Formula - Nested Ifs

Current Nested IF formula is below:

=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,)))))))))))))))))))))

 

[DKRbella112316]

Re: Need Help with Formula - Nested Ifs

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,)))))))))))))))))))))

 

[DKRbella112316]

Re: Need Help with Formula - Nested Ifs

The complete formula I have written is not posting - can someone help?!?

 

[Joe4]

Re: Need Help with Formula - Nested Ifs

We need to see that formula in order to try to figure out what might be wrong with it.
However, you have not successfully posted that formula for us yet!
That is because the < > signs in your formula are being mistaken for HTML code.
See my previous post for instructions on how to post that formula so that it shows in its entirety.
(be sure to read all the replies in your threads!)

 

[DKRbella112316]

Re: Need Help with Formula - Nested Ifs

We need to see that formula in order to try to figure out what might be wrong with it.
However, you have not successfully posted that formula for us yet!
That is because the < > signs in your formula are being mistaken for HTML code.
See my previous post for instructions on how to post that formula so that it shows in its entirety.
(be sure to read all the replies in your threads!)

Hello, I have downloaded the HTML add-in and have followed the instructions; however, I do not see any output generated from right clicking on the cell which contains formula and selecting 'Generate HTML (Default)'. Please advise.

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,)))))))))))))))))))))