# I am finding it difficult to enter a formula to calculate the discount!

1. ## I am finding it difficult to enter a formula to calculate the discount!

Hi all, I have attached the Excel 2010 Workbook which I am referring to in this thread.

I am finding it difficult to enter a formula to calculate the discount. The 12% discount is only applicable to 10 OR MORE of the SAME products ordered (for example, if 10x 17" wheels only are ordered and 7x 18" wheels only are ordered, then the discount is only applicable for the 17" wheels. However if 10x 17" wheels only are ordered AND 10x 18" wheels only are ordered, then 12% discount is given EACH to BOTH of the products).

I want to know how to calculate the total discount (in cell F25). I have tried using the IF function and the LOOKUP function but both times it has become complicated and hard to get my head around.

Moderator's note: I have deleted the file, I just noticed it contains sensitive info (email and pp number)

2. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Hi and welcome to the forum

All sorts of ways to do this, here is 1...
=D8*E8*IF(E8>=10,0.88,1)

3. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Originally Posted by FDibbins
Hi and welcome to the forum

All sorts of ways to do this, here is 1...
=D8*E8*IF(E8>=10,0.88,1)
thank you!

I tried that but a number still shows up if I enter 5 as the quantity?

4. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Well isnt that supposed to happen? Unit Price*Qty = Total...the total would be shown no matter what, it would be adjusted down if 10 or more are sold?

15" Wheels Only...£120...5...£600.00
15" Wheels Only...£120...10...£1200.00
but with the discount applied...1056

5. ## Re: I am finding it difficult to enter a formula to calculate the discount!

 C D E F 7 DESCRIPTION UNIT PRICE QUANTITY TOTAL (EX VAT) 8 15" Wheels Only £120 10 £1,056.00 9 16" Wheels Only £160 10 £1,600.00 10 17" Wheels Only £200 £0.00 11 18" Wheels Only £245 £0.00 12 19" Wheels Only £330 £0.00 13 15" Wheels with Budget Tyres £165 £0.00 14 16" Wheels with Budget Tyres £215 £0.00 15 17" Wheels with Budget Tyres £235 £0.00 16 18" Wheels with Budget Tyres £365 £0.00 17 19" Wheels with Budget Tyres £515 £0.00 18 15" Wheels with Premium Tyres £185 £0.00 19 16" Wheels with Premium Tyres £235 £0.00 20 17" Wheels with Premium Tyres £270 £0.00 21 18" Wheels with Premium Tyres £420 £0.00 22 19" Wheels with Premium Tyres £570 £0.00 23 SUBTOTAL (EX VAT) £2,656.00 24 VAT £531.20 25 DISCOUNT £144.00 26 DELIVERY CHARGE (EX VAT) £40.00 27 TOTAL (EX VAT) £2,552.00

F8=D8*E8*IF(E8>=10,0.88,1) copied down
F25=SUMPRODUCT(D8:D22,E8:E22)-SUM(F8:F22)
F26=SUM(E8:E22,20)

6. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Originally Posted by FDibbins
Well isnt that supposed to happen? Unit Price*Qty = Total...the total would be shown no matter what, it would be adjusted down if 10 or more are sold?

15" Wheels Only...£120...5...£600.00
15" Wheels Only...£120...10...£1200.00
but with the discount applied...1056
oh yeah of course:P

but how would I write one formula for the total discount? so instead of the formula being only for total of 15" wheels, how would I do it for all of the parts in one formula?

7. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Originally Posted by FDibbins
 C D E F 7 DESCRIPTION UNIT PRICE QUANTITY TOTAL (EX VAT) 8 15" Wheels Only £120 10 £1,056.00 9 16" Wheels Only £160 10 £1,600.00 10 17" Wheels Only £200 £0.00 11 18" Wheels Only £245 £0.00 12 19" Wheels Only £330 £0.00 13 15" Wheels with Budget Tyres £165 £0.00 14 16" Wheels with Budget Tyres £215 £0.00 15 17" Wheels with Budget Tyres £235 £0.00 16 18" Wheels with Budget Tyres £365 £0.00 17 19" Wheels with Budget Tyres £515 £0.00 18 15" Wheels with Premium Tyres £185 £0.00 19 16" Wheels with Premium Tyres £235 £0.00 20 17" Wheels with Premium Tyres £270 £0.00 21 18" Wheels with Premium Tyres £420 £0.00 22 19" Wheels with Premium Tyres £570 £0.00 23 SUBTOTAL (EX VAT) £2,656.00 24 VAT £531.20 25 DISCOUNT £144.00 26 DELIVERY CHARGE (EX VAT) £40.00 27 TOTAL (EX VAT) £2,552.00

F8=D8*E8*IF(E8>=10,0.88,1) copied down
F25=SUMPRODUCT(D8:D22,E8:E22)-SUM(F8:F22)
F26=SUM(E8:E22,20)

OH thank you VERY MUCH!!!!

8. ## Re: I am finding it difficult to enter a formula to calculate the discount!

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

Also, please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

9. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Ok I will FDibbins
So you mean like this reply?
And last question, how would I make the delivery charge = "£0.00" if there are no quantities selected?

10. ## Re: I am finding it difficult to enter a formula to calculate the discount!

If you meant F26, try this...
=IF(F23=0,0,SUM(E8:E22,20))

11. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Yes that's worked!
Thank you very much for your help!!

12. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Oh ive got a last question!
I want to put the discount result as a minus sign (if there is a discount), how would I do that?

13. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Swap the signs in F25...
=-SUMPRODUCT(D8:D22,E8:E22)+SUM(F8:F22)
change F27 to...
=F23+F26+F25

14. ## Re: I am finding it difficult to enter a formula to calculate the discount!

thank you it worked

15. ## Re: I am finding it difficult to enter a formula to calculate the discount!

Originally Posted by rmp123
Hi, about the same workbook.. how would I make the delivery charge ONLY APPLICABLE if the subtotal is less than 18000? i.e the delivery charge will be zero even if +10 products are ordered IF the subtotal is MORE THAN 180000?
Change it to...
=IF(OR(F23=0,F23>18000),0,SUM(E8:E22,20))

thank you!

17. ## Re: I am finding it difficult to enter a formula to calculate the discount!

How would I calculate the delivery charge of a cell E8? the delivery charge is £20 + EACH set of wheels ordered i.e. dependant on the quantity...?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1