Discounts on excel

edreid95

New Member
Joined
Nov 23, 2013
Messages
8
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
you can use IF

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

But what happens if you have more than 5 ?
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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...
 
Last edited:
Upvote 0
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 :eek:
 
Upvote 0
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
 
Upvote 0
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),""))))
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0


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

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top