How do I calculate two different prices based on a given quantity?

I want to be able to calculate a price based on a given quantity. But here is the trick.

Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.

If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15

But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.

How do I make this formula work?

That works, but what about if we are looking up the case pack number (12) and the price from a separate data sheet.

For instance:

In sheet one, we input in A1 the item number for a product, in A2 the case pack is drawn in from A1 on sheet two, which is our data information.
Then in A3 from sheet one, our price comes in, which is drawn from A2 on sheet two.
On sheet two, I have added a column A3 which has the upcharge of 15% added to the original price of each item.

Where do I put the formula now? Do I have to nest it within another formula?

Thanks,

Anyone have a possible solution yet?

I have entered in that formula, but the correct price does not show. I don't think the formula is complete. Maybe I am not explaining the equation properly.

Here goes:
If quantity ordered is evenly divisable by case pack number, than price structure A is used. ie. 24 units ordered, the units come in boxes of 12. So 24 divides equally into 12, giving the price structure A. ie. 24 x 1.00 = $24.00

But, if 25 units are ordered, than the first two boxes of 12 are calculated at price structure A, and the remaining 1 unit is priced at a 15% upcharge and added to the total. ie. 24 x 1.00, plus 1 x 1.15 = $25.15

A running total is taken of all the items ordered and the final price is shown at the end.


FORM
A1 is where the item number is typed in
B1 is where the ordered quantity is typed in
C1 is the case pack number that gets pulled in from DATA D =IF(A25=0," ",VLOOKUP(A1,DATA!A:D,4,FALSE))
D1 is where the item description gets pulled in from DATA B =IF(A25=0," ",VLOOKUP(A1,DATA!A:C,2,FALSE))
L1 is where the price per unit gets pulled in from DATA C =IF(A25=0," ",VLOOKUP(A1,DATA!A:D,3,FALSE))
M1 is the price total =IF(A1=0," ",B1*L1)

DATA
A1 is the item number
B1 is the item description
C1 is the price per unit
D1 is the case pack number
E1 is the unit price at upcharged by 15%

Sorry for the confusion, but I hope this sets it straight. I appreciate all your work for me.

that worked wonderfully.

Wow, thank you very much.

Okay, I have another question. Let's say that we no longer want customers to be able to have an upcharge of 15% on broken case packs. We only want them to order in case pack quantities.

If I want the customers to only order items in case pack quantities, than where do I put that calculation? Would it go in M1?

For instance, if an item comes in packs of 12. Then the customer is only allowed to enter a number that is greater than 12 and divisable. ex. 12,24,26,48,60 etc.

They would not be allowed to order less than 12. Like 6 or 4 or 5 or 13 or 26, etc.

So if the customer input a number other than multiples of 12 and higher in cell B1, the price should not be calculated.
I would like the order quantity to either round to the nearest multiple of 12 and perform the calculated price or simply highlight the cell B1 in red.

Thanks