I have designed the attached spreadsheet, and am using the “Portion” figures as work fields to be used for generating the “Totals” figures, as you can see by the formulas. Note that the "Portion" is based on the "Proportion" for that item.
My real data has a lot of rows in the top group, and not many in the bottom group, and I’d like to calculate the “Totals” figures without needing the “Portion” figures at all, if possible.
Any ideas on how I can achieve this? I’m assuming the “Totals” formulas can be complexified to do this, but I don’t know how.
Ideally I'd like this to work in Excel 2003, but if that can't easily be done, 2007 would be OK.