Two Different Percentages

Can anyone help with an explanation as to why Excel returns one percentage result - 6.86% - in a cell and a different percentage result - 6.42% - in the related pie chart, please? (Image attached.)

(Answering the obvious first question - yes, the chart is referring to the correct cells, as you can see by the $ values in both chart and cells.)

Thanks in anticipation!

Originally Posted by tuph

Can anyone help with an explanation as to why Excel returns one percentage result - 6.86% - in a cell and a different percentage result - 6.42% - in the related pie chart, please? (Image attached.)

(Answering the obvious first question - yes, the chart is referring to the correct cells, as you can see by the $ values in both chart and cells.)

Thanks in anticipation!

Hi,

.jpg's are nice, they're good for concealing errors.

6.42 is the % that the small piece is of the pie

6.86 is the % that the small piece is of the large piece

try something like

=D1/(D1+E1) =E1/(D1+E1)

where D1 and E1 are your two slices.

hth
---

Originally Posted by Bryan Hessey

Hi,

.jpg's are nice, they're good for concealing errors.

6.42 is the % that the small piece is of the pie

6.86 is the % that the small piece is of the large piece

---

Sorry, Bryan - should have know to post a better example. See attached.

eeerrrrrm - don't understand your explanation. The percentage is the difference between sales made and budget, so it should be the same in both places (but I'm probably missing something ). The chart works in the other 11 workbooks I have reporting on similar data for other sales regions.

Originally Posted by tuph

Sorry, Bryan - should have know to post a better example. See attached.

eeerrrrrm - don't understand your explanation. The percentage is the difference between sales made and budget, so it should be the same in both places (but I'm probably missing something ). The chart works in the other 11 workbooks I have reporting on similar data for other sales regions.

two figures, the 78608 and the 5390

5390 is 6.8% of 78608

5390 is 6.4% of (78608+5390) = the total pie.

Unfortunately your figures make no sense, a budget of 78,000 and a Sales of 5,000 means that you won't have the problem for very long.

does this make more sense?

ps, which attachment?

---

added, if the 5,000 is the difference between Sales and Budget then the Total pie should be 78,000 budget and the Sales should be 78,000 - 5,000, Sales = 73,000

---

ps, which attachment?

! Attached this time.

Unfortunately your figures make no sense, a budget of 78,000 and a Sales of 5,000 means that you won't have the problem for very long.

Very true. This is the first week of the sales promotion I'm reporting on. My concern is that if I've got something wrong I need to address it.

Actually, I'm not convinced that a pie chart is the best way to do it, but that's what management wants. Once a branch goes over budget I remove the percentages from the chart anyway because they are completely misleading. Might just do that from the beginning.

Thanks for your help. I have a much better understanding now.

Have a great weekend! (It's Friday evening here.)

Originally Posted by tuph

! Attached this time.


Very true. This is the first week of the sales promotion I'm reporting on. My concern is that if I've got something wrong I need to address it.

Actually, I'm not convinced that a pie chart is the best way to do it, but that's what management wants. Once a branch goes over budget I remove the percentages from the chart anyway because they are completely misleading. Might just do that from the beginning.

Thanks for your help. I have a much better understanding now.

Have a great weekend! (It's Friday evening here.)

Hi,

I agree that the % on the Pie are misleading, but it's the Pie itsself that is wrong

A Pie chart represents 100%, and you divide the 100% into slices to see who gets or did what share.

Your Budget is 78000, and that should be 100% of your Pie

Your sales of 5,000 mean that you have 5,000 budgetedactual sales, and 73,000 budgeted that never became actual.

Your chart should reflect a total Pie of 78,000 of which 5,000 is a pretty green (go the sales) and 73,000 is a murky brown (some sales persons are not up to budget)

You are trying to do an 83,000 Pie, and then, of course, your % are incorrect.

Hope this helps you to understand, and enjoy the weekend.

note, it was amusing to note that your attachment was a coloured chalkboard with nary a formula in sight.

---

Originally Posted by Bryan Hessey

You are trying to do an 83,000 Pie, and then, of course, your % are incorrect.

Hope this helps you to understand, and enjoy the weekend.

note, it was amusing to note that your attachment was a coloured chalkboard with nary a formula in sight.

1) Yeah - that's why I'm trying to convince management that this is NOT an appropriate feedback option.

2) C&*p!! I have two versions - the master and the one that's sent out, which is values only - and did the zip file in a hurry.

Thanks for your patience!

Originally Posted by tuph

1) Yeah - that's why I'm trying to convince management that this is NOT an appropriate feedback option.

2) I have two versions - the master and the one that's sent out, which is values only - and did the zip file in a hurry.

Thanks for your patience!

the obvious answer is to produce a printed pie chart where Budget and Sales both = 78,000 and if anyone queries the new approach produce that chart and ask if that is what they intend? (it looks ridiculous)

Then a Bar chart of the same figures and compare the sanity.

Otherwise adjust your 78,000 to be 73,000 (budget - Sales) and list the slices as Sales and additional Budgeted

hth
---

Actually the answer is even simpler than that. I had one of those DOH! moments this morning when preparing the weekly reports. I had selected the wrong source data!!!

I should have been comparing $sales with $shortfall which, of course, add up to $budget - my 100% figure. It's all working properly now.

Thanks again, Bryan, for bouncing this one around.

Originally Posted by tuph

Actually the answer is even simpler than that. I had one of those DOH! moments this morning when preparing the weekly reports. I had selected the wrong source data!!!

I should have been comparing $sales with $shortfall which, of course, add up to $budget - my 100% figure. It's all working properly now.

Thanks again, Bryan, for bouncing this one around.

you are quite correct, instead of saying "budgeted that never became actual" I should have used the correct "shortfall" term