Discount Function Required

Freq_Flyer

New Member
Joined
Jan 27, 2009
Messages
3
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.
 
Last edited:

Freq_Flyer

New Member
Joined
Jan 27, 2009
Messages
3
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.
 

Freq_Flyer

New Member
Joined
Jan 27, 2009
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,210
Messages
5,413,096
Members
403,463
Latest member
kraamerica

This Week's Hot Topics

Top