Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a series, then average
I'm new to VB, so I apologize in advance for what may be a simple
problem: I'm trying to search a worksheet, based on the intersection of column and row headers, for a series of cells, then average the numbers in those cells, and output that average to a different worksheet. Any help would be much appreciated. SB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a series, then average
assume Row 1 of the data sheet contains headers like (starting in B1)
Col1 Col2 Col3 . . . Assume Column A has headers like rw1 rw2 rw3 rw4 starting in A2 Sub GetAverage() Dim rHdr(1 to 3, 1 to 2) as String Dim res as Variant, res1 as Variant Dim r(1 to 3) as double rHdr(1,1) = "Col1": rHdr(1,2) = "Rw3" rHdr(2,1) = "Col5": rHdr(2,2) = "Rw7" rHdr(3,1) = "Col10": rHdr(2,3) = "Rw9" for i = 1 to 3 With Worksheets("Data") res = application.Match(rHdr(i,1),.Rows(1),0) res1 = application.Match(rHdr(i,2),.Columns(1),0) End With set rng = Intersect(cells(1,res).EntireColumn,cells(res1,1). EntireRow) r(i) = rng.Value Next worksheets("Output").Range("F9").Value = Application.Average(r) end Sub -- Regards, Tom Ogilvy wrote in message ups.com... I'm new to VB, so I apologize in advance for what may be a simple problem: I'm trying to search a worksheet, based on the intersection of column and row headers, for a series of cells, then average the numbers in those cells, and output that average to a different worksheet. Any help would be much appreciated. SB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding a line to show average for a data series on a chart | Charts and Charting in Excel | |||
Find series | Excel Discussion (Misc queries) | |||
average function for a series | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Can I add an average series to a chart with 2 or more series? | Charts and Charting in Excel |