Promotional pricing formula (buy 3 items get cheapest half price)

[fishsticks]

Sorry if this has been asked before but I dont know how to solve this problem and dont know what to search (I have been googling all day with no success..)

I want to create a quoting sheet in excel that will automatically calculate promotion pricing.

It will be buy 3 items get the cheapest at a 50% discount.

My input sheet will look like:

Items	Quantity	Price	TOTAL
Apple	4	$20	$80
Orange	2	$10	$20
Pear	4	$15	$60
TOTAL	10		$160

So in this example there are 10 items in TOTAL which means the 3 cheapest (2 apples & 1 orange) will be discounted by 50%.

This would be 2x oranges and 1x pear $35 x 50% = $17.50 discount

So the order total would be $160-$17.50 = $142.50

I thought I could use the SMALL function to find the 3 smallest (and use QUOTIENT to find the number 3) but I dont know how to find the 3 smallest values when my list isnt broken into individual lines (each line has its own quantity)

...I have no idea where to start really.

If this is possible, can somebody please help me with a formula for this? Or at least point me in the right direction?

Cheers,

Kieran

[Jacc]

Wow! How can something that seems so harmless be that difficult? Or is my solution totally off?

I managed to come up with some sort of solution but I don't consider it complete.
I sort of patched up most of the errors but I would like to have it pick out which fruits gets the discount and that's more than I can handle.

I put random numbers in for Quantity and Price as a form of testing. Pressing F9 (recalc with new random numbers) and checking the numbers is actually entertaining for a while.

[FCarv]

Wow! How can something that seems so harmless be that difficult?

Exactly. I am trying this for a long time but i'm more focus on deliver a unique formula. I was thinking in a IF the cheapest price >=3 items, bam, ifnot cheapest price =2, bam2, if not chapest price =1, bam3, "". .. something like this.
I have managed a way to get almost all the formula done tough i'm having difficulties on having the return of the quantities of the cheapest price.

I already tried index with small(array;1) and offset(small(array;1);0;-1) but I can't put none to work.

[NickyC]

Jacc
I agree, this one is tough!

I'm almost embarrassed by the route I got there, but I think this code delivers the outcome desired

it's in two formats, a macro and a user-defined function

in either case you will need to enter the range to evaluate, and the discount rate (as a fraction, ie 0.5)

I have assumed a range with at least three columns, the first being product name, the second the quantities, the third the prices

If the input range is not in this format, it won't work

also, the range must only include the items, not the title rows or total row at the end

Option Base 1 must be at the top of the module

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[fishsticks]

Wow, thanks for all the help everyone, what a great forum!

Jacc, I really like the idea you are trying to achieve in your example, the completed part of the sheet seems to work great

If there was a way to display which products were being discounted, this would be perfect! I only realised after posting that this would be a useful feature, not only for simply seeing the workings but if it could return all of the lines of the order with the discounted lines separated at their new price, I could simply copy and paste this into my order entry software.


NickyC - the macro seems to work perfectly, from my testing it does appear to ignore the top row and allow a title row (which is useful)

The function would be a great/tidy solution but it doesn't quite work properly. It doesn't discount any item when there are only 3 items, it only starts discounting items when there is 6 or more and when it does it chooses the lowest prices to discount.

e.g. There are with 3 items it doesn't discount, with 6 items it discounts only the cheapest item, with 9 items it discounts the 2 cheapest.


It is getting late so I will have a look again in the morning, and try learn from all the examples above, thanks again for all the help!

[Jacc]

Hi fishsticks
Always good to hear the feedback

I realize now that I didn't read the rules properly. My sheet gives a 50% discount on the three cheapest items regardless of the number of items.
Now that I (think I) understand the rules it just means it's even more difficult, aaargh!

[fishsticks]

The function would be a great/tidy solution but it doesn't quite work properly. It doesn't discount any item when there are only 3 items, it only starts discounting items when there is 6 or more and when it does it chooses the lowest prices to discount.

e.g. There are with 3 items it doesn't discount, with 6 items it discounts only the cheapest item, with 9 items it discounts the 2 cheapest.

I don't completely understand how this code works but by adding a "+1" after discountNumber near the end, it appears to work as advertised.

Please Login or Register  to view this content.

Jacc - sorry I didnt make the problem as clear as I should have, it should be for every 3 whole items discount the cheapest 1.

eg.
2 apples = no discount
3 apples = 50% discount on cheapest 1
7 apples = 50% discount on cheapest 2

I like how you were trying to solve the problem though by providing a list of which products are actually being discounted.

[Jul85]

I downloaded your sheet and its great, but yes our promotion is the same, its buy2 get the 3rd 50% and it should keep counting how many items are purchased.

have you managed to find a solution to it since 2012? :D