complex discounting programs

[jcc5018]

I am hoping that I can make this work in excel, though it may be better suited for a database program.
I am looking to create a series of automatic discounts for my products and I am hoping to find a nice way to automate this for invoicing purposes.

I kind of know what i need to do, but im not sure if it is possible in excel.


here is my table format, Image#, product ID, Name, Collection, QTY, and Price.

so when a client selects an image, they may select it on one or multiple products.

I have two kinds of discounts I need to create.

1. BOGO-- if 1<QTY<4 price = Price+ (qty-1)*price*discount

This one I think I've got, though is there an easy way to repeat the equation for each qty above 4 (or the specified value)? so if a qty of 12 for example, the would pay full price for every 5th item, and discount would be applied to the remainder.

2. discount if the same image is used among different products.
This would need to search the image column for any duplicates
for each unique image find the max product price associated with that image. store value as Base1
for each remaining image/ product combo, check to see if the collection matches that of BASE 1, If yes, discount =30%, if no, discount =20%

Again, this may be better suited for a PHP function or something, but If i can make it work in excel, that would be really helpful.

[natefarm]

If you could provide an attachment with sample data and desired results, that would be very helpful. Thanks.

[jcc5018]

Here is a sample sheet. I posted the desired results as well as a few other questions.

sample.xlsx