Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004

    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.


  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland

    Re: Dynamically created spreadsheet - % Calculations

    Hi TIA

    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts