Discounts on excel

[edreid95]

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

 

[etaf]

you can use IF

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

But what happens if you have more than 5 ?

 

[DutchDiggy]

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:

	A	B	C	D	E	F	G	H	I	J	K
Item
Football
Sweatshirt

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]Pricing[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]QTY[/TD]
[TD="align: center"]Sales[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"] € 7,50 [/TD]
[TD="align: center"]5[/TD]
[TD="align: center"] € 7,00 [/TD]
[TD="align: center"]10[/TD]
[TD="align: center"] € 6,75 [/TD]
[TD="align: center"]25[/TD]
[TD="align: center"] € 6,50 [/TD]
[TD="bgcolor: #FFFF00, align: center"][/TD]
[TD="bgcolor: #FFFF00"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"] € 21,00 [/TD]
[TD="align: center"]2[/TD]
[TD="align: center"] € 20,00 [/TD]
[TD="align: center"]5[/TD]
[TD="align: center"] € 18,00 [/TD]
[TD="align: center"]25[/TD]
[TD="align: center"] € 15,50 [/TD]
[TD="bgcolor: #FFFF00, align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"]90[/TD]

</tbody>

Blad1​

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

 

[edreid95]

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]

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]

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]

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]

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]

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]

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