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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,563
Office Version
365
Platform
MacOS
you can use IF

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

But what happens if you have more than 5 ?
 

DutchDiggy

Board Regular
Joined
Sep 10, 2013
Messages
210
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:

edreid95

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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,563
Office Version
365
Platform
MacOS
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
Joined
Sep 10, 2013
Messages
210
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
Joined
Nov 23, 2013
Messages
8
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
Joined
Oct 24, 2012
Messages
3,563
Office Version
365
Platform
MacOS
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
Joined
Nov 23, 2013
Messages
8


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
 

Forum statistics

Threads
1,089,461
Messages
5,408,397
Members
403,201
Latest member
jenmears

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top