Q:

Math for Buy X Get Y For Z

I'm working on a POS software that require a Buy X Get Y For Z discount schema, i.e: Buy 5 Get 2 For 5$, it means if you buy 7 items, 5 items are normal price and 2 items (6th, 7th) are 5$.
This is the spreadsheet for this https://docs.google.com/spreadsheets/d/1ym93Xqnw6wupBEp9ei711wQPpt3s6QONjcqBO4Xc5X4/edit#gid=0
I want to a algorithm to get X and Y (discounted item) when input quantity
i.e: input quantity and it will return X and Y for Buy 5 Get 2
input 7 return X = 5, Y= 2

input 8 return X = 6, Y= 2
..

input 17 return X= 13,Y= 4

I'm trying to find formula for this one but I'm failed. Please help me thanks

A:

x = 5
y = 2
i = input

r = i % (x + y)
n = (i - r) / (x + y)
py = max(0, r - x) + (n * y)
px = i - py 

return x = px, y = py

To explain, I'm setting r with the modulus/remainder of input / (x + y). This is the number remaining after completed offers are removed. I am then setting n to be the number of complete offers by subtracting the remainder from the input and dividing by (x + y). The variable py is then set using n * y for the number of items at the discounted price for completed offers and adding r - x if that is > 0. Finally px is the number of items at full price which is simply the input value - py.
In your spreadsheet, you have not implemented this correctly. Change as follows:
G2    =A2-F2
H2    =G2/($L$1+$L$2)
D2    =MAX(0,F2-$L$1)+H2*$L$2
E2    =A2-D2

