Hello.
I hope that someone will be able to help me with this issue....

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 - depends on the users selection criteria.

The column data are as follows:
Branch - Column B
Turnover - Column E
%Turnover - Column I


Someone suggested pivot tables. Is this a bit far-fetched??
I was thinking of using the $ character plus some VBA code
I have all the information in the spreadsheet - just need the %Turnover.

Any help will be much appreciated.

TIA

Hi
pivot tables would also be my recommendation :-)
- no formulas required
- easy to create and maintain
- no code required.
- the user can easily select specific groups for analysis

for getting started with them see:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Frank Kabel
Frankfurt, Germany


axapta wrote:
Hello.
I hope that someone will be able to help me with this issue....

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 - depends on the users selection criteria.

The column data are as follows:
Branch - Column B
Turnover - Column E
%Turnover - Column I


Someone suggested pivot tables. Is this a bit far-fetched??
I was thinking of using the $ character plus some VBA code
I have all the information in the spreadsheet - just need the
%Turnover.

Any help will be much appreciated.

TIA

this looks to me like a simple formula in column I

=RC5/sum(C5)

if you want code:

Sub ApplyFormula()
dim rowcount as long
rowcount = range("E2").End(xlDown).Row
with range(Range("I2"),cells(rowcount,"I"))
.FormulaR1C1 = "=RC5/sum(C5)"
end with
End Sub

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"axapta" wrote in message
...
Hello.
I hope that someone will be able to help me with this issue....

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 - depends on the users selection criteria.

The column data are as follows:
Branch - Column B
Turnover - Column E
%Turnover - Column I


Someone suggested pivot tables. Is this a bit far-fetched??
I was thinking of using the $ character plus some VBA code
I have all the information in the spreadsheet - just need the %Turnover.

Any help will be much appreciated.

TIA

Hello Patrick
I tried your formula but this does not work. I get an error message saying you can't use formulas in a pivot table
I have attached the spreadsheet to a previous thread

Frank: I have had a look at the web-sites and tried to implement a solution using pivot tables. This isn't quite what I need. The user wants to see all the records. They do not need to do any other manipulation/sorting. This has already been done for them

Kind Regards

I've got an algorithm.... Will this work

Starting Point Cell B
CurrentTurnover = Cell
MoveNex
If CurrentCell = StartingPointCell & Not = StartingPointCell & 'total
'still within the group so add the Turnover as part of the running tota
TotalTurnover = CurrentTurnover + CurrentCell(E
els
StartingPoint= Cell we are now on - New Grou
MoveNex
If CurrentCell = StartingPointCell & Not = StartingPointCell & 'total
'still within the new group so add the Turnover as part of the running tota
TotalTurnover = CurrentTurnover + CurrentCell(E

EndI
ColumnI = ColumnE/TotalTurnover*10

Will this work? I do not know the equivalent VBA but if you think this solution could work, can you please supply the VBA

Kind regard

I opened your file - although I'd NEVER recommend doing so. Your data starts
in row 5 - the code below worked fine. As far as I can see, the data table
is not a pivot table. If you want formula in a pivot table, then add a pivot
field defined as a formula.

Sub ApplyFormula()
Dim rowcount As Long
rowcount = Range("E52").End(xlDown).Row
With Range(Range("I5"), Cells(rowcount, "I"))
.FormulaR1C1 = "=RC5/sum(R5C5:R" & rowcount & " C5)"
End With
End Sub


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"axapta" wrote in message
...
Hi Frank,
I will look at the sites...
I have attached a sample of the spreadsheet.
There is a macro which sums the totals for each group.
It is the last 2 columns which I will presumably need a pivot table for...

If you could help me further, this will be much appreciated. I will try
and
work out a solution from the web-sites you have given but this is quite
urgent...

Kind Regards

"Frank Kabel" wrote in message
...
Hi
pivot tables would also be my recommendation :-)
- no formulas required
- easy to create and maintain
- no code required.
- the user can easily select specific groups for analysis

for getting started with them see:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Frank Kabel
Frankfurt, Germany


axapta wrote:
Hello.
I hope that someone will be able to help me with this issue....

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 - depends on the users selection criteria.

The column data are as follows:
Branch - Column B
Turnover - Column E
%Turnover - Column I


Someone suggested pivot tables. Is this a bit far-fetched??
I was thinking of using the $ character plus some VBA code
I have all the information in the spreadsheet - just need the
%Turnover.

Any help will be much appreciated.

TIA

The following is framework for the code you are looking for to create a new
row sub total after each change in the Branch
Assumes 'Branch' is in column A and your column C, needs development to put
in the row % based on each subtotal, HTH
Uses the first blank in column A to stop the processing.
Cheers
Nigel

Public Sub SubTotal()
Dim CurrentRow As Long
Dim SubTotal As Long
Dim LoopControl As Boolean
Dim Group As String
' begin process
CurrentRow = 5
SubTotal = 0
While Len(Trim(Cells(CurrentRow, 1).Value)) 0
'test current position with next row
Group = Cells(CurrentRow, 1).Value
While Group = Cells(CurrentRow, 1).Value
SubTotal = SubTotal + Cells(CurrentRow, 3)
CurrentRow = CurrentRow + 1
Wend
'insert a row to add total for group
Rows(CurrentRow).Insert Shift:=xlDown
Cells(CurrentRow, 1).Value = "Total"
Cells(CurrentRow, 3).Value = SubTotal
' reset subtotals and move to next row after previous total
SubTotal = 0
CurrentRow = CurrentRow + 1
Wend
End Sub

Cheers
Nigel

"axapta" wrote in message
...
I've got an algorithm.... Will this work?

Starting Point Cell B5
CurrentTurnover = Cell E
MoveNext
If CurrentCell = StartingPointCell & Not = StartingPointCell & 'total'
'still within the group so add the Turnover as part of the running total
TotalTurnover = CurrentTurnover + CurrentCell(E)
else
StartingPoint= Cell we are now on - New Group
MoveNext
If CurrentCell = StartingPointCell & Not = StartingPointCell & 'total'
'still within the new group so add the Turnover as part of the running
total
TotalTurnover = CurrentTurnover + CurrentCell(E)

EndIF
ColumnI = ColumnE/TotalTurnover*100


Will this work? I do not know the equivalent VBA but if you think this
solution could work, can you please supply the VBA.

Kind regards