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

**jstnvndn** · 08-29-2006, 04:36 PM

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?

**VBA Noob** · 08-29-2006, 04:52 PM

Hi,

Say amount in A1 then enter this in B1

=(A1-MOD(A1,12)*1)+(MOD(A1,12)*1.15)

VBA Noob

**jstnvndn** · 08-30-2006, 12:25 PM

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,

**jstnvndn** · 09-01-2006, 11:09 AM

Anyone have a possible solution yet?

**SteveG** · 09-01-2006, 01:54 PM

You are brining in 2 of the components into A2 and A3 of sheet 1. If you also bring in the upcharge to say A4 and enter your order quantity in A5 then,

=(A5-MOD(A5,A2)*A3)+(MOD(A5,A2)*A4)

HTH

Steve

**daddylonglegs** · 09-01-2006, 08:52 PM

That could be simplified to

=A3*A5+MOD(A5,A2)*(A4-A3)

**jstnvndn** · 09-05-2006, 03:40 PM

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.

**SteveG** · 09-05-2006, 04:22 PM

Using daddylonglegs simplified version with the change from subtraction to multiplication at the end

=L1*B1+MOD(B1,C1)*((L1*0.15)*L1)

This returned $25.15 as expected using your Form layout. Formula is input in M1.

HTH

Steve

**jstnvndn** · 09-05-2006, 05:21 PM

that worked wonderfully.

Wow, thank you very much.

**SteveG** · 09-06-2006, 09:05 AM

You're welcome. Thanks for the feedback.

Cheers,

Steve

**jstnvndn** · 09-06-2006, 03:16 PM

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

**SteveG** · 09-06-2006, 03:54 PM

You could restrict the values that the customer entered into B1 of the Form. To do so you could use Data Validation. Click on B1, go to Data>Validation and select "Custom" in the Allow drop down. In the Formula bar enter,

=MOD(B1,C1)=0

On the Error Alert Tab select Stop and name your alert whatever you'd like. This will prompt the person inputting the data to retry or cancel their entry.

HTH

Steve

**jstnvndn** · 09-07-2006, 03:09 PM

That worked out great.

Thanks for your help, you have been a fantastic resource.

**jstnvndn** · 08-30-2007, 03:45 PM

One more question.

An item comes shipped in a case of 12. (there are other demoninations as well, like 6 or 4)

If you buy a case of 12, then the price shows up as $1.00.

If you buy less than 12, then I want the price to show up as $1.15.
Which is a 15% upcharge.

I have calculated all the regular prices in DATA column C. And all the upcharge prices in DATA column E.

How do I get the price on the order form to show the regular price when they order in case pack quantities, and then show the upcharge price in the same field if they order less than the casepack quantity?

If they order for example 13 pieces, and the case pack is 12. I would still like to show the price as the regular price.

It's the less than case pack price that is important to flip back and forth on.

Big Thanks.

**jstnvndn** · 08-30-2007, 03:48 PM

sorry.

here is the sample file that I am working on.

**jstnvndn** · 09-04-2007, 10:50 AM

Sorry to be a pest, I have been trying to make this calc work, but to no avail with my limited knowledge.

I am leaving an a bus. trip on Wednesday and need this to work.

Can anyone help out?