| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range average code
Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
you didn't mention what the output should be
Sub SaveAvg6() Set rngSource = ThisWorkbook.Sheets(1).Columns(2) Set wbNew = Workbooks.Add Set rngOutput = wbNew.Sheets(1) j = 1 For i = 2 To 2500 Step 6 rngOutput.Cells(j, 1) = Application.WorksheetFunction.Average(rngSource.Ce lls(i).Resize(6, 2)) j = O + 1 Next i End Sub "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
Hi,
Not quite as elegant as the one already provided, but it works. Sub Avg6Again() Range("B1").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)" Selection.Copy ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, -1).Range("A1:B1").Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Loop End Sub -- David "voodooJoe" wrote: you didn't mention what the output should be Sub SaveAvg6() Set rngSource = ThisWorkbook.Sheets(1).Columns(2) Set wbNew = Workbooks.Add Set rngOutput = wbNew.Sheets(1) j = 1 For i = 2 To 2500 Step 6 rngOutput.Cells(j, 1) = Application.WorksheetFunction.Average(rngSource.Ce lls(i).Resize(6, 2)) j = O + 1 Next i End Sub "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
The output would be in col.F, not in a new workbook and it would be average of the range. Thanks Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
Hi saziz,
If you wanted a pure formula solution, try something like: =IF(MOD(ROW(B1),6)=0,AVERAGE(OFFSET(A1,0,0,6,1))," ") That will average the values in column A every 6 rows. Just an idea. Regards, Peter Beach "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
Hi David, Thank you. The average is for the whole range ("B2:C7"). and instead of inserting a row the average could be in cell D7. Thank you Syed David Wrote: Hi, Not quite as elegant as the one already provided, but it works. Sub Avg6Again() Range("B1").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)" Selection.Copy ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, -1).Range("A1:B1").Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Loop End Sub -- David "voodooJoe" wrote: you didn't mention what the output should be Sub SaveAvg6() Set rngSource = ThisWorkbook.Sheets(1).Columns(2) Set wbNew = Workbooks.Add Set rngOutput = wbNew.Sheets(1) j = 1 For i = 2 To 2500 Step 6 rngOutput.Cells(j, 1) = Application.WorksheetFunction.Average(rngSource.Ce lls(i).Resize(6 2)) j = O + 1 Next i End Sub "saziz" wrot in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone ca help me write a VBA code. first range value is (B2:C7) and then next would be further 6 row down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread http://www.excelforum.com/showthread...hreadid=498143 -- sazi ----------------------------------------------------------------------- saziz's Profile: http://www.excelforum.com/member.php...nfo&userid=635 View this thread: http://www.excelforum.com/showthread.php?threadid=49814 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
Hi Again,
Try this one: Sub Avg6Again() Range("B2").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(6, 0).Select ActiveCell.Offset(0, 4).FormulaR1C1 = "=AVERAGE(R[-6]C[-4]:RC[-3])" Loop End Sub -- David "Peter Beach" wrote: Hi saziz, If you wanted a pure formula solution, try something like: =IF(MOD(ROW(B1),6)=0,AVERAGE(OFFSET(A1,0,0,6,1))," ") That will average the values in column A every 6 rows. Just an idea. Regards, Peter Beach "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
Hi again saziz,
Just to be clear, if you copy the formula below straight down all cells in column B (in my example) you will have the effect I think you are after of showing the average of each block of six values interpersed with cells that appear blank. I presume the problem you are having is that it is inconvenient to have to copy then paste every 6 cells down a column of 2,500 rows :-( Regards, Peter Beach "Peter Beach" wrote in message ... Hi saziz, If you wanted a pure formula solution, try something like: =IF(MOD(ROW(B1),6)=0,AVERAGE(OFFSET(A1,0,0,6,1))," ") That will average the values in column A every 6 rows. Just an idea. Regards, Peter Beach "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
David, Thank you for help. I had to change the range a bit. But it worked perfectly. Syed David Wrote: Hi Again, Try this one: Sub Avg6Again() Range("B2").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(6, 0).Select ActiveCell.Offset(0, 4).FormulaR1C1 = "=AVERAGE(R[-6]C[-4]:RC[-3])" Loop End Sub -- David "Peter Beach" wrote: Hi saziz, If you wanted a pure formula solution, try something like: =IF(MOD(ROW(B1),6)=0,AVERAGE(OFFSET(A1,0,0,6,1))," ") That will average the values in column A every 6 rows. Just an idea. Regards, Peter Beach "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range average code
Hi Peter, Thank you for your help. I did not have any blank cells in the range. All I wanted was to average each block of 6 rows B2:C7. and put that average (output) in col d7. David's code helped except I had to slightly change the range. thank you Syed Peter Beach Wrote: Hi again saziz, Just to be clear, if you copy the formula below straight down all cells in column B (in my example) you will have the effect I think you are after of showing the average of each block of six values interpersed with cells that appear blank. I presume the problem you are having is that it is inconvenient to have to copy then paste every 6 cells down a column of 2,500 rows :-( Regards, Peter Beach "Peter Beach" wrote in message ... Hi saziz, If you wanted a pure formula solution, try something like: =IF(MOD(ROW(B1),6)=0,AVERAGE(OFFSET(A1,0,0,6,1))," ") That will average the values in column A every 6 rows. Just an idea. Regards, Peter Beach "saziz" wrote in message ... Hi Folks, I have a sheet with data in col. B & C 2500 rows. I do average on every 6th. row down until 2,500 rows. I find myself doing this quite often. Wondering if someone can help me write a VBA code. first range value is (B2:C7) and then next would be further 6 rows down starting from (B8:C13) until where the data ends (about 2500 rows) Thank you for your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=498143 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| average if - between range | Excel Worksheet Functions | |||
| Average last 3 in range | Excel Worksheet Functions | |||
| AVERAGE a range in a column if another column's range equals a val | Excel Discussion (Misc queries) | |||
| Average of a , < range | Excel Worksheet Functions | |||
| Average Range | Excel Worksheet Functions | |||
![saziz[_53_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
