Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Figuring list price - cost to show discount percentage

1. I agreed to these rules
Join Date
25th April 2006
Posts
5

## Figuring list price - cost to show discount percentage

Ok, I'm mathmatically challenged, but I love Excel an figure this should be pretty easy. Here is my problem.

List Price \$46.98 (e2)
Net Cost \$19.53 (e3)

How do I enter a calculation that will show me my discount percentage from my supplier? (e4)

I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item. Thank you!

Theresa

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Figuring list price - cost to show discount percentage

Originally Posted by Sindril
Ok, I'm mathmatically challenged, but I love Excel an figure this should be pretty easy. Here is my problem.

List Price \$46.98 (e2)
Net Cost \$19.53 (e3)

How do I enter a calculation that will show me my discount percentage from my supplier? (e4)

I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item. Thank you!

Theresa
Formula for cell E4 should be =ROUND(100*(E2-E3)/E2,2)

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
25th April 2006
Posts
5

## Re: Figuring list price - cost to show discount percentage

I appreciate your help so very much and have a question. When I use your formula I get 57.13%. When I use the calculator I get 58.4%. Any idea why the large disparity?

Sindril

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Figuring list price - cost to show discount percentage

Well, could you re-check what you posted was what you have in your sheet? I get 58.43, formula and calculator.

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
25th April 2006
Posts
5

## Re: Figuring list price - cost to show discount percentage

Yeppers, I should have said F2 NOT E3:

=ROUND(100*(E2-F2)/E2,2)

Now it works just dandy! Except that it shows 5843.00. Can I ask one final question? How do I get it to put the decimal in the correct place, which in this case would make it 58.43? Again, thank you!

Sindril

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Figuring list price - cost to show discount percentage

I would say impossible, but what do you have in E2, F2?

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Figuring list price - cost to show discount percentage

What have you got in F2 then? The problem with percentages is that it depends how you have the cell formatted. Really, the answer should be 58.43%, which means formatting cell E4 as a percentage, with 2 decimal places (go to Format, Cell, Number) and then the actual value of the cell should be 0.58. Test it by putting "=1*E4" in another cell. Depending on the format, you might need to remove the "100*" from the formula.
Last edited by ByTheCringe2; April 25th, 2006 at 05:31.

Excel Video Tutorials / Excel Dashboards Reports

8. I agreed to these rules
Join Date
25th April 2006
Posts
5

## Re: Figuring list price - cost to show discount percentage

E2 = \$46.98
F2= \$19.53
G2= 5843.00%

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Figuring list price - cost to show discount percentage

Theresa,

if G2 is formatted as a percentage, then remove the part *100

Excel Video Tutorials / Excel Dashboards Reports

10. I agreed to these rules
Join Date
25th April 2006
Posts
5

## Re: Figuring list price - cost to show discount percentage

Okay, you are right. Once I changed the cell to number instead of % is shows me 58.43 which is exactly what I needed to see. I know it is a percentage and that's all that counts in my particular application.

Have an awesome day!
Sindril

Excel Video Tutorials / Excel Dashboards Reports

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
•
porno