Excel 2003 Charts

[TWB]

Possible bug in Excel 2003. Trying to chart Funds spent to date for each
account. We have 3 colums (spent to date, Total available to spend and % of
Total Spent). Instead of charting % of total spent, it appears Excel is
adding the Spent to date and Total Value columns and charting that in the
Stacked bar chart. Anyone have input for usign the stacked bar charts?

Thanks much.

 

[Jon Peltier]

Reply to this post with a sample of your data pasted into the message (i.e.,
no attachment). I've been using Excel 2003 since, well, 2003, and I've never
noticed such an issue.

- Jon

 

[TWB]

Here is the data:

Acct Spent to date Total Value % of Total Spent
1001 $160,079 $250,000 64.03%
1002 $40,967 $50,000 81.93%
1003 $50,240 $100,000 50.24%
1004 $0 $50,000 0.00%
1005 $8,559 $30,000 28.53%
1006 $2,727 $30,000 9.09%
1007 $10,503 $30,000 35.01%
1008 $7,194 $30,000 23.98%
1009 $310,205 $430,000 72.14%
$590,474 $1,000,000 59.05%

It appears that Excel is charting the Spent to Date + Total Value = "Value"
then Spent to Date / "Value". For example for Acct 1002 instead of the
Stacked bars showing that 81% is spent the chart is showing 45% which is
40967 + 50000 = 90967 Then 40967 / 90967= 45%. I hope this is making sense.

Thanks.

 

[Del Cotter]

It appears that Excel is charting the Spent to Date + Total Value = "Value"
then Spent to Date / "Value". For example for Acct 1002 instead of the
Stacked bars showing that 81% is spent the chart is showing 45% which is
40967 + 50000 = 90967 Then 40967 / 90967= 45%. I hope this is making sense.

If you are using the 100% Stacked Bar Chart and you would like the Spent
to date to appear as a percentage of the total available, you must make
the second value be Total *Not* Spent to date, so that the two values
together make up the total available.

So your charted values must be:

Spent Unspent
1001 160,079 89,921
1002 40,967 9,033
1003 50,240 49,760
1004 0 50,000
1005 8,559 21,441
1006 2,727 27,273
1007 10,503 19,497
1008 7,194 22,806
1009 310,205 119,795