Discount Function Required

[Freq_Flyer]

Hi There,

My first post to this forum. thank you everyone who answers these questions.

I need an Excel formula that computes a discount such that:

f(1) = 10 f(2) = 20 f(3) = 30 f(4) = 40 f(5) = 40

f(6) = 50 f(7) = 60 f(8) = 70 f(9) = 80 f(10) = 80

f(11) = 90 f(12) = 100 f(13) = 110 f(14) = 120 f(15) = 120 etc.

Essentially, the formula is f(x) = 10x, but it 'discounts' every x divisible by 5.

Any input would be greatly appreciated

Dan

 

[nbrcrunch]

are you saying that the numbers represent percentages?

to figure 10% of 100, the formula would be: =100*.10

If cell A1 contains the value 100, then the formula would be: =A1*.10

Not sure if this answers your question.

 

[Freq_Flyer]

hi nbrcrunch, thanks for your speedy reply.

no, it's not really for calculating percentages. what it is for is to discount the fifth item in any set of five items that someone orders. so, for example, let's say i want to offer someone lessons in guitar, and i want to give the person every fifth lesson free. they decide to order 12 lessons. so assuming that the lessons are $10 each (i know, this price is unrealistic, but this is a hypothetical example), they will not pay $120, but will instead pay $100, because 2 of the 12 lessons are offered for free. this situation is represented by my function notation example below, f(12) = 100. i have a feeling that it would use some sort of modulus function, but my discrete math skills are oh so rusty...

does this clarify a bit?

cheers, and thanks again for taking the time to reply.

dan

 

[nbrcrunch]

How do you plan on actually representing that to the customer on a receipt? If you are going to itemize purchases and show every 5th line-item discounted, that should be straight-forward. On the otherhand, if you are attempting to workout a single formula that will total the whole bill-of-sale, that would be a bit more challenging.

So first use the count function: =COUNT() to count the number of unit prices on the receipts and divide by 5. If your prices are in column C, =INT(COUNT(C1:C80)/5)

The INT() function will give you the whole number of items to be discounted.

The rest of it is something I'd need to see in ordr to work out. But there are others here that can visualize solutions better than I can.

 

[Richard Schollar]

If x is in A1 then it sounds to me like:

=10*(A1-INT(A1/5))

 

[Freq_Flyer]

Richard,

Thanks so much! The formula you gave did it! Thanks also to nbrcrunch for thinking through the problem with me!

You guys are awesome and your work is much appreciated...

Cheers

dan