# Automatic calculation of cheapest unit price with bulk discounts

1. ## Automatic calculation of cheapest unit price with bulk discounts

Hi,

Firstly sorry about the title - sometimes it's difficult trying to explain what you want.

In my sample file I have three columns with prices for 2 products (Columns BDF). What I would like excel to automatically calculate is whether it is cheaper to buy more of the product for the decreased price or to stick with the quantity required.

For example I have already worked out manually (text in red) that it is cheaper to buy 500 of Product A at the second price (90p) than buying 451 of Product A at the first price (£1).

Is there a way to get excel to calculate this automatically? I would like to use this on a spreadsheet that has 30 items on it and 6 different price breaks. Prices are going up all the time so I don't fancy having to recalculate everytime there is a price increase. i can add as many columns as Excel 2007 will allow.

Any help greatly appreciated - even if it's a resounding NO it can't be done!!

Thanks

2. ## Re: Automatic calculation of cheapest unit price with bulk discounts

Hi

Where do you nominate what the minimum quantity for each product you require?

rylo

3. ## Re: Automatic calculation of cheapest unit price with bulk discounts

Hi rylo,

The minimum quantity will be 200 as there are no price breaks before this. This would be the case for 22/30 of the products.

The minimum ordering quantity is dealt with in a different cell in a different part of th workbook. If a qty under 200 is entered then the figure is rounded up to 200.

Thanks

4. ## Re: Automatic calculation of cheapest unit price with bulk discounts

Hi

Guess I asked the wrong question. Where is the quantity required to be entered. You have mentioned 451in your example for Product A, but where is this required amount entered? Is it the same for every product?

rylo

5. ## Re: Automatic calculation of cheapest unit price with bulk discounts

Hi Rylo,

The example I have given isn't exactly the sheet I need it for. The quantity in question isn't the number required of a product, the quantity is for square metres of corrugated board. This is calculated by multimplying 2 numbers together so the quantity isn't so much entered as worked out. I currently have a 2.8mb excel file which serves us as a costing sheet and an orders file also with various other admin sheets added along the way.

For the purposes of this example the quantity can be entered into cell A5 (I will change the forumla to point to the correct cell when I put it into my work file. This formula will be used 60 times in each worksheet as there is a possibility that is the maximum number of instances this calculation will be required.

Thanks

6. ## Re: Automatic calculation of cheapest unit price with bulk discounts

Hi

Is this what you are trying to achieve?

rylo

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1