Nested IF for tiered pricing structure?

**ziyal9** · 09-24-2011, 10:29 PM

**Added question at end**

I'm not even sure what keywords I would use to search for this solution...

My school is selling holiday wreaths and one type we're buying wholesale comes in cases of 12 (for a discounted price) or cases of 6. Based on how many orders we get for this type of wreath, how do I calculate how many cases of 12 to order versus how many cases of 6 and then how many will be leftover? I thought I'd done it by dividing the # of orders into 12 and then rounding down and moving the remainder into the 'cases of 6' column (=ROUNDDOWN(SUM(H25/H31),0). But then I tested an order of 23 and realized that I should only ever have to get one case of 6 because 2 cases of 6 would bring me to 1 case of 12. So what functionality do I use to calculate how many cases of 12 I need versus how many cases of six I need (which only ever be one) and then calculate for the leftovers.

Does this even make sense- my head is swimming! The sad part is that I could do this in two seconds in my head or on a calculator but I've spent 2 hours trying to figure it out in Excel. I've demoted myself back to Excel Novice. =(

Thanks!

******************************
So I've used the formulas suggested (included below)- thanks *so* much! And now I need to figure out how to calculate if the leftover wreaths (remainders) are in the $7.25 (12 case) pricing tier or in the $7.50 (6 case) tier.

Can anyone help with that part?

Code:
B1: =INT(A1/12)+(MOD(A1,12)>6)
and an addition in D1 to show wreaths remaining:

Code:
D1: =(12*B1+6*C1)-A1

**JBeaucaire** · 09-24-2011, 10:36 PM

If the total number of boxes is in A1, then:

B1: =INT(A1/12) (this is how many boxes of 12 to buy
C1: =IF(B1*12<A1, 1, 0) This is how many boxes of 6 to buy.

**ConneXionLost** · 09-25-2011, 01:54 AM

A suggested change to JB's first formula:

B1: =INT(A1/12)+(MOD(A1,12)>6)

and an addition in D1 to show wreaths remaining:

D1: =(12*B1+6*C1)-A1

Cheers,

**ConneXionLost** · 09-26-2011, 12:00 AM

Hi ziyal9,

If you have a follow-up question, it's usually easier for all concerned if you posted a reply to the thread rather than editing your original post.

To show the pricing tier for the remaining wreaths, take advantage of the formula in C1, and add this IF formula to the E1 cell:

E1: =IF(C1,"remaining at $7.50 per wreath","remaining at $7.25 per wreath")

Cheers,

**ziyal9** · 09-26-2011, 08:29 PM

I thought I was finished with this and originally marked it 'SOLVED' but it turns out that I did not articulate well exactly what I need. I've entered all of the code suggested above and it's beautiful (thanks so much!). The last part I'm having a huge problem with (to the point of tears) is trying to figure out how to get the number of leftover wreaths at the $7.25 price to show in the cell under the # of 12-Case Bundles Column cell and the leftover wreaths at the $7.50 price to show in the 6-Case Bundles column cell next to it. The goal being that someone can easily look at this section of two columns by two rows and see how many bundles of 12- cases and how many bundles of 6-cases to order and underneath each cell see the related number of leftover wreaths attached to that price point (obviously, the leftovers will show under one column or another and not both). I'd like this also so I can use those cell values later in the spreadsheet for totaling purposes.

Please, please, please help me be done with this! BTW, I am learning a ton- I hadn't used the INT and MOD functions before! I've attached the spreadsheet if you want to see it. It's clean- I promise! =)

~ Heidi