Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the average of two generated totals
Good afternoon,
I am currently using the following code: Dim r As Range For Each r In Sheet1.UsedRange.Columns(1).SpecialCells(xlCellTyp eConstants).Areas With r(r.Rows.Count + 1).Resize(, 8) .Value = .Offset(-1).Value .Offset(, 1).Resize(, 1).Value = WorksheetFunction.Sum(r.Offset(, 1)) .Offset(, 3).Resize(, 1) = WorksheetFunction.Sum(r.Offset(, 3)) .Offset(, 4).Resize(, 1) = WorksheetFunction.Sum(r.Offset(, 4)) .Font.Bold = True End With Next r to summarise groups of like transations (that are seperated by blank rows) by calculating the totals and values of each set and placing them in the row below said group of transactions eg: (bold doesn't seem to come out, the bottom row is the totals/summary) DATE QTY B/S PRICE Gross Sale CUR ACC SERVICE 20090727 16997 S 66.5 1130300.5 GBX DEFAULT DMA 20090727 48606 S 66.5 3232299 GBX DEFAULT DMA 20090727 10000 S 66.5 665000 GBX DEFAULT DMA 20090727 58316 S 66.5 3878014 GBX DEFAULT DMA 20090727 133919 S 266 8905613.5 GBX DEFAULT DMA This process is repeated for each group of transactions (20+) that are on the worksheet. However, my Price column total is not meant to be a sumtotal, but instead the average which is the Gross Sales/QTY. Is there a way I can modify my code to do that? Would really appreciate any help or suggestions that can be given. Regards, PVANS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the average of two generated totals
Instead of using worksheetfunction.sum(), try worksheetfunction.average().
PVANS wrote: Good afternoon, I am currently using the following code: Dim r As Range For Each r In Sheet1.UsedRange.Columns(1).SpecialCells(xlCellTyp eConstants).Areas With r(r.Rows.Count + 1).Resize(, 8) .Value = .Offset(-1).Value .Offset(, 1).Resize(, 1).Value = WorksheetFunction.Sum(r.Offset(, 1)) .Offset(, 3).Resize(, 1) = WorksheetFunction.Sum(r.Offset(, 3)) .Offset(, 4).Resize(, 1) = WorksheetFunction.Sum(r.Offset(, 4)) .Font.Bold = True End With Next r to summarise groups of like transations (that are seperated by blank rows) by calculating the totals and values of each set and placing them in the row below said group of transactions eg: (bold doesn't seem to come out, the bottom row is the totals/summary) DATE QTY B/S PRICE Gross Sale CUR ACC SERVICE 20090727 16997 S 66.5 1130300.5 GBX DEFAULT DMA 20090727 48606 S 66.5 3232299 GBX DEFAULT DMA 20090727 10000 S 66.5 665000 GBX DEFAULT DMA 20090727 58316 S 66.5 3878014 GBX DEFAULT DMA 20090727 133919 S 266 8905613.5 GBX DEFAULT DMA This process is repeated for each group of transactions (20+) that are on the worksheet. However, my Price column total is not meant to be a sumtotal, but instead the average which is the Gross Sales/QTY. Is there a way I can modify my code to do that? Would really appreciate any help or suggestions that can be given. Regards, PVANS -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use sub-totals and average in same calculation | Excel Worksheet Functions | |||
Access totals, sum, average | Excel Discussion (Misc queries) | |||
Calculations using totals generated in Pivot Tables | Excel Discussion (Misc queries) | |||
Finding Procedure which generated error | Excel Programming | |||
Finding Totals...Continued | Excel Programming |