var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Dynamically created spreadsheet - % Calculations
I have the following in a spreadsheet. The spreadsheet is created dynamically - an array of data is passed to it from my ASP Page.
The columns are fixed, however the rows will change.
How can I work out the %Turnover? - I've worked it out manually below.
The calculation will be: Turnover/Total Turnover(for (each) Group)*100
Branch Account No Turnover %Turnover
Cambridge 1 50.00 44.23%
Cambridge 2 48.00 42.48%
Cambridge 3 15.00 13.27%
Cambridge Total 113.00 <- Total Turnover(for the Group)
There can be many groups - this depends on the users selection.
Branch - Column B
Turnover - Column E
%Turnover - Column I
Someone suggested pivot tables. Is this is a bit far-fetched??
I have all the information in the spreadsheet - just need the %Turnover.
Any help - be it a solution using Pivot tables - will be much appreciated.
Re: Dynamically created spreadsheet - % Calculations
One way you could do this is in Cell I2 put this formula
=E2/SUMPRODUCT((B$2:B$65000 = B2)*($E$2:$E$65000))
i Have assumed a couple of things here.
Firstly you dont know how many rows are going to be on the Transfer.
(If you Do change 65000 to your last row it will be quicker)
2nd You have a Title row
3rd if there is any blank rows an error will occur in the blank row you have the "#DIV/0!"
4th that your column is already in the format of a percentage
Otherwise just copy this formula down to your last filled row