Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Creating a dynamic asset allocation chart
I was inspired by a recent E-Trade commercial to build a financial portfolio
pie chart using Excel. The chart I have in mind would show the percent breakdown of asset allocation (e.g., cash, large cap stocks, small cap stock, etc). This is the data table format I had in mind: Financial Institute (column 1), Allocation (column 2), and Amount (column 3) e.g., BofA Cash $1600 Fidelity Large Cap $1000 Fidelity Cash $101 As I update my allocation, say, move some cash to small cap stocks, the chart would automatically display my new portfolio breakdown. The problem I'm running into is this: when I try to create such a pie chart, each row (series) is created as its own slice (e.g., BofA cash, Fidelity Large Cap, Fidelity cash) rather than grouping a single "cash" category. Is it possible for Excel to dynamically look for cells with the same content (say, "Cash") and add the corresponding amount to form a single slice? Thanks in advance! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Creating a dynamic asset allocation chart
Use your data as the source for a PivotTable. The PT will have the
'allocation' as the row field and SUM('amount') as the data field. Alternatively, you can "roll your own." Suppose your data are in A2:Cn and row 1 is a header row. Then, create a named formula (Insert | Name Define...) DataRng =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,3) The use of the above name means the solution will adjust itself as you add more rows. In some empty range, enter the category names (the column 2 names). Cash Large Cap etc. Then in an adjacent cell, array enter the formula =SUM(IF(INDEX(DataRng,0,2)=F2,INDEX(DataRng,0,3))) Copy this cell as far down the column as you have categories in the previous column. [To array enter a formula, do not complete data entry with the ENTER key. Instead, use the CTRL+SHIFT+ENTER combination. If done correctly, XL will display the formula within curly brackets { and }] -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I was inspired by a recent E-Trade commercial to build a financial portfolio pie chart using Excel. The chart I have in mind would show the percent breakdown of asset allocation (e.g., cash, large cap stocks, small cap stock, etc). This is the data table format I had in mind: Financial Institute (column 1), Allocation (column 2), and Amount (column 3) e.g., BofA Cash $1600 Fidelity Large Cap $1000 Fidelity Cash $101 As I update my allocation, say, move some cash to small cap stocks, the chart would automatically display my new portfolio breakdown. The problem I'm running into is this: when I try to create such a pie chart, each row (series) is created as its own slice (e.g., BofA cash, Fidelity Large Cap, Fidelity cash) rather than grouping a single "cash" category. Is it possible for Excel to dynamically look for cells with the same content (say, "Cash") and add the corresponding amount to form a single slice? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Chart | Charts and Charting in Excel | |||
Dynamic Step Chart using range names | Charts and Charting in Excel | |||
Dynamic chart | Charts and Charting in Excel | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Creating an x,y coordinate chart | Charts and Charting in Excel |