# Discounts on excel

#### edreid95

##### New Member
Hi there, i was just wondering how and if you can create an offer on excel. For example i work in a sports shop and i have created this spreadsheet and on it am i selling footballs for £7.50. I have created formula's where once you type the quantity next, it totals the price up and inserts it into another cell, however i want to include an offer where if the quantity is 5, then i want the total to come up at £35.00 rather than the actual price of £37.50. Can anyone help? Thanks

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### etaf

##### Well-known Member
you can use IF

=if( Quantity=5, 35.0, quantity*7.5)

But what happens if you have more than 5 ?

#### DutchDiggy

##### Board Regular
Because you've got more than one item in the shop, you'd problably want the discount differ each product. I would set up like this:

ABCDEFGHIJK
2ItemPricingQTYSales
3Football1 € 7,50 5 € 7,00 10 € 6,75 25 € 6,50
4Sweatshirt1 € 21,00 2 € 20,00 5 € 18,00 25 € 15,50 590

</tbody>

In K3 put this formula: =IF(J3>=H3,J3*I3,IF(J3>=F3,J3*G3,IF(J3>=D3,J3*E3,ALS(J3>=B3,J3*C3,""))))

shoot... I've made a mistake.... It'll only work if every discount is entered... if you just have 1 discount-amount, it won't work.. wait up... gonna change that...

Last edited:

#### edreid95

##### New Member
you can use IF

=if( Quantity=5, 35.0, quantity*7.5)

But what happens if you have more than 5 ?
If they buy 5 then the price per ball goes down to £7.00 therefore making a total of £35.00.If they buy more than 5 then the price goes back to its orginial at £7.50. However if you purchase 10 then the price per ball will go down to £6.50 totaling at £65.00. The cell C26 is the price (£7.50), cell D26 is the quantity and the cell E26 is ther total. Could you please write out the whole formula please? Currently, cell E26 has a formula of =C26*D26. Thanks

#### etaf

##### Well-known Member
do the get the 5 at 7.00 and then each one after the 5 to 9 is 7.50 ?
for example if purchase 6 - is that 5*7 = 35.00 + 7.50
or
6*7.50

#### DutchDiggy

##### Board Regular
Was away for a while, but this is the formula for cell K3:

=IF(H3>0, IF(J3>=H3,J3*I3,IF(J3>=F3,J3*G3,IF(J3>=D3,J3*E3,IF(J3>=B3,J3*C3)))),IF(F3>0, IF(J3>=F3,J3*G3,IF(J3>=D3,J3*E3,IF(J3>=B3,J3*C3))),IF(D3>0, IF(J3>=D3,J3*E3,IF(J3>=B3,J3*C3)),IF(B3>0, IF(J3>=B3,J3*C3),""))))

#### edreid95

##### New Member
do the get the 5 at 7.00 and then each one after the 5 to 9 is 7.50 ?
for example if purchase 6 - is that 5*7 = 35.00 + 7.50
or
6*7.50
between 1 and 4, the buyer pays £7.50 per ball. From 5 to 9, they pay £7.00 and from 10+ they pay £6.50 per ball so if they were to purchase 6 then they would pay £42.00 (6*£7.00)
Thanks

#### etaf

##### Well-known Member
The cell C26 is the price (£7.50), cell D26 is the quantity and the cell E26 is ther total

between 1 and 4, the buyer pays £7.50 per ball. From 5 to 9, they pay £7.00 and from 10+ they pay £6.50 per ball so if they were to purchase 6 then they would pay £42.00 (6*£7.00)

so in E26 put

=if( d26<5, 7.50*d26 if(d26<10, 7.0*d26, d26*6.5))

#### edreid95

##### New Member

so in E26 put

=if( d26<5, 7.50*d26 if(d26<10, 7.0*d26, d26*6.5))
I put in your equation and this came up in cell E26 '#VALUE!'.....

I had a look at your equation and this is what i came up with '=IF( D26<5, C26*D26) IF(4<D26<10, 7*D26) IF(D26>10,6.5*D26)' but is still unfrotunately wrong. Can you try and amend it?

Thanks for all your help so far mate

#### edreid95

##### New Member
=if( d26<5, c26*d26) if(4<d26<10, 7*d26) if(d26>10,6.5*d26)