Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for averaging
Hello All:
Would you please help with macro to average all the data and put in a new sheet. 1/ "Rawdata" sheet contain all the data 2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4, and 5-7, so on) 3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2 4/ Delete all the blank rows Thanks for all your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for averaging
I don't know what you are refereing to by blank rows.
You can use a formula for getting the averages if you are smart by using the row number of where the formula is located. On the summary sheet starting a row 3 the start row on RawData is where X is the Row number For Row 2 Start Row = (3*(X-2)) + 2 For X:3 = 2 X:4 = 5 X:5 = 8 X:6 = 11 End Row = (3*(X-3)) + 4 For X:3 = 4 X:4 = 7 X:5 = 10 X:6 = 13 So a formula could be for Row 2 =SUM(INDIRECT("RawData!"&((3*(ROW(C2)-2)) + 2)&":"&((3*(ROW(C2)-2)) + 4))) Row 2 Row 3 This formula will produce =Sum(Indirect("RawData!5:7")) Row 4 This formula will produce =Sum(Indirect("RawData!8:10")) Then copy formula dow the column. "bioyyy" wrote: Hello All: Would you please help with macro to average all the data and put in a new sheet. 1/ "Rawdata" sheet contain all the data 2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4, and 5-7, so on) 3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2 4/ Delete all the blank rows Thanks for all your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for averaging
Joel:
Great formula. However, the value give me so different if I do manuallly (average(Rawdata!C2:C4)? Any suggestions Thanks, "Joel" wrote: I don't know what you are refereing to by blank rows. You can use a formula for getting the averages if you are smart by using the row number of where the formula is located. On the summary sheet starting a row 3 the start row on RawData is where X is the Row number For Row 2 Start Row = (3*(X-2)) + 2 For X:3 = 2 X:4 = 5 X:5 = 8 X:6 = 11 End Row = (3*(X-3)) + 4 For X:3 = 4 X:4 = 7 X:5 = 10 X:6 = 13 So a formula could be for Row 2 =SUM(INDIRECT("RawData!"&((3*(ROW(C2)-2)) + 2)&":"&((3*(ROW(C2)-2)) + 4))) Row 2 Row 3 This formula will produce =Sum(Indirect("RawData!5:7")) Row 4 This formula will produce =Sum(Indirect("RawData!8:10")) Then copy formula dow the column. "bioyyy" wrote: Hello All: Would you please help with macro to average all the data and put in a new sheet. 1/ "Rawdata" sheet contain all the data 2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4, and 5-7, so on) 3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2 4/ Delete all the blank rows Thanks for all your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for averaging
The formula should be
from average(Rawdata!C2:C4) to average(Rawdata!2:4) You are only averaging 3 cells not the entire row. I would first try SUM instead of Average to see if sum works. when an average is performed the number of cells that it divides by may be incorrect. I'm not sure what criteria excel uses for the division. blank cells may be making the division incrooect. Also if you have other data in the rows beside number this may cause problems. Dates will be treated as numbers and added to the average. I like using the Evaluate formula tool to help solve problems. Click on cell with number and then from the menu Tools - formula Auditing - Evaluate formula.. "bioyyy" wrote: Joel: Great formula. However, the value give me so different if I do manuallly (average(Rawdata!C2:C4)? Any suggestions Thanks, "Joel" wrote: I don't know what you are refereing to by blank rows. You can use a formula for getting the averages if you are smart by using the row number of where the formula is located. On the summary sheet starting a row 3 the start row on RawData is where X is the Row number For Row 2 Start Row = (3*(X-2)) + 2 For X:3 = 2 X:4 = 5 X:5 = 8 X:6 = 11 End Row = (3*(X-3)) + 4 For X:3 = 4 X:4 = 7 X:5 = 10 X:6 = 13 So a formula could be for Row 2 =SUM(INDIRECT("RawData!"&((3*(ROW(C2)-2)) + 2)&":"&((3*(ROW(C2)-2)) + 4))) Row 2 Row 3 This formula will produce =Sum(Indirect("RawData!5:7")) Row 4 This formula will produce =Sum(Indirect("RawData!8:10")) Then copy formula dow the column. "bioyyy" wrote: Hello All: Would you please help with macro to average all the data and put in a new sheet. 1/ "Rawdata" sheet contain all the data 2/ Average 3 rows starting from row 2 to 2000 (let say average row 2 to 4, and 5-7, so on) 3/ Copy and paste in a new sheet lets called "Summary" at column 3 row2 4/ Delete all the blank rows Thanks for all your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging | Excel Worksheet Functions | |||
averaging & ??? | Excel Worksheet Functions | |||
Averaging best 15 out of 20? | Excel Discussion (Misc queries) | |||
More Averaging | Excel Worksheet Functions | |||
Averaging again | Excel Worksheet Functions |