# Discount Function Required

#### Freq_Flyer

##### New Member
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### nbrcrunch

##### Well-known Member
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

Last edited:

#### Freq_Flyer

##### New Member

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
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

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

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

#### Freq_Flyer

##### New Member
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