chart your data automatically
 

heloo guys

suppose i have a sheet like this (4 column)

1 intel 30 30%
2 microsoft 20 20%
3 att 10 10%
4 bell 20 20%
5 sony 10 10%
6 lg 10 10%

as the forth colum is the percentage of every company over the total
30%= 30/(30+20+10+20+10+10)

and every time you open the sheet you may add some data
How you can chart that data automatically ?
as a pie
as each company percentage will be a piece in the pie chart
please answer me in details....
note i allready calculated the percentage...
i want the charting matter


--
amrezzat
------------------------------------------------------------------------
amrezzat's Profile: http://www.excelforum.com/member.php...o&userid=28766
View this thread: http://www.excelforum.com/showthread...hreadid=488225

chart your data automatically
 

I think you want to go with Dynamic Named Ranges for both the chart's
Category Labels and Values. These use worksheet functions (particularly the
Offset function) in the ranges' RefersTo property to allow automatic
adustment of the range definitions. Debra Dalgleish discusses this:
http://www.contextures.com/xlNames01.html

Example:
1. Open the Define Name dialog: Select Insert Names Define
2. Now create the Category Labels range:
i. In the "Names in workbook:" window enter the name "CatLabels"
ii. In the "Refers to:" window enter the following formula:
=OFFSET(Sheet1!$B$1, 0, 0, MAX(COUNTA(Sheet1!$B:$B), 1), 1)
(See <Note below)
iii. Click the Add button
3. Now create another range for the Values:
i. In the "Names in workbook:" window enter the name "Vals"
ii. In the "Refers to:" window enter:
=Offset(CatLabels, 0, 1)
iii. Click the Add button
4. Click the Close button
5. Now change the chart's source data to reference these names:
i. Right-click the chart and select Source Data
ii. Select the Series tab (if not already active)
iii. Ensure that "Series1" is selected in the "Series" window
iv. In the "Values:" window enter: =Sheet1!Vals
v. In the "Category Labels:" window enter: =Sheet1!CatLabels

The above assumes that the worksheet is named "Sheet1". Also assumed is that
the Category Labels (stock names) are all in column B and there are no other
data below this. The same is assumed for the column C data (number of shares
?). As you add/delete data to these ranges the chart should update
automatically. I never use Pie charts but it is evident that they
automatically calculate the percentages, so the fourth column (D) is not
required by the chart.

As far as the fourth column is concerned, I would:
1. Insert this formula in cell D1:
=IF(C1 = "", "", 100*C1/SUM(Vals)).
2. AutoFill the forumla down to the maximum range that you would ever need.

Alternative, use code in the WorksheetChange event to automatically adjust
this range to match the adjacent Dynamic Named ranges or just AutoFill them
as required when you add/delete the stock data.

< Note The conventional formula would have been:
=OFFSET(Sheet1!$B$1, 0, 0, COUNTA(Sheet1!$B:$B), 1)
The formula I used is a slight departure that includes the Max function to
ensure that the range definition is never empty (at least one cell) and thus
avoids an error if there are no data.

The Copy and Paste functions are not available when either the Define Names
and Source Data dialogs are active. To copy use:
<Ctrl + C
To paste use:
<Ctrl + V

Regards,
Greg