Creating a dynamic asset allocation chart

[humble_t]

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!

 

[Tushar Mehta]

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