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]

sorry missed a comma out

try this
=IF( D26<5, 7.5*D26, IF(D26<10, 7*D26, D26*6.5))

 

[edreid95]

For some reason, when i reply on here, it missed out parts of the formula!
=IF( D26<5, C26*D26) IF(4<D26<10, 7*D26) IF(D26>10,6.5*D26)

 

[etaf]

leave a space between the characters , as it can try and do a web tag

 

[oldbrewer]

1 to 4	7.5								1	7.5
5 to 9	7								5	7.25
10 to 14	6.75								10	6.75
15 to 19	6.5								15	6.5
20 plus	6.25								20	6.25
how many	price each
7	7.25
the table on the right
is called mytable
formula in price each cell
=VLOOKUP(A14,mytable,2)
you can put extra columns in my tabl
for golf balls and ping pong balls

<colgroup><col span="3"><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[edreid95]

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

 

[edreid95]

You did it!!!!!!!!!! thanks so sos so much!!!!