Macros - Average every 5th cell!!!!??

macros

New Member
Joined
Jul 29, 2009
Messages
7
I have an excel sheet with time in column A and scientific data in column B. I would like to get a macro that averages every 5 values/cells.

THANK YOU
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Code:
=IF(MOD(ROW(),5)=0),AVERAGE(B1:B100))
Enter with CTRL + SHIFT+ENTER
lenze
 
Upvote 0
Lenze,
thank you for you quick reply!!
I tried it out... pasting it in and holding CTRL+SHIFT+ENTER.
I keep getting an error: This formula contains an error.
Is there anything I'm doing wrong?
Do you know how to write the actual macro code for it?


THANK YOU!!!
 
Upvote 0
Average of the 1st , 6th, ... and so on cells of range B1:B100:
=AVERAGE(IF(MOD(ROW(B1:B100),5)=ROW($B$1),B1:B100))
Its an array formula - apply CSE
 
Last edited:
Upvote 0
OOPS....I actually meant averaging ever 5 cells not every 5th cell, ...cells 1 through 5, 6 thourh 10, 11 through 15, etc.
SORRY!
Can you help me with that?
 
Upvote 0
Put this (not array) formula to C1 and then copy it to C2:C100 range:

=IF(MOD(ROW(B1),5),"",AVERAGE(B1:OFFSET(B1,-4,0)))

In C5, C10, C15 etc the averages of 5 sequential cells will be shown
 
Last edited:
Upvote 0
ZVI,
thank you for your response!!
I tried the formula but got an error message.
=IF(MOD(ROW(B1),5),"";AVERAGE(B1:OFFSET(B1,-4,0)))
Do I need to add anything between the ""?
Thank you
 
Upvote 0
ZVI,
thank you for your response!!
I tried the formula but got an error message.
=IF(MOD(ROW(B1),5),"";AVERAGE(B1:OFFSET(B1,-4,0)))
Do I need to add anything between the ""?
Thank you
There was misprint, use comma , char instead of semicolon ; char:
=IF(MOD(ROW(B1),5),"",AVERAGE(B1:OFFSET(B1,-4,0)))
It was fixed in my previous post during 10 minutes.
 
Last edited:
Upvote 0
like this?
Excel Workbook
ABC
120:4624
206:2889
314:5754
416:3686
509:396062.6
618:1025
721:0996
812:4041
916:2072
1005:329064.8
1110:2732
1206:0867
1306:3665
1413:3242
1510:436754.6
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C1=IF(MOD(ROW(),5)<>0,"",AVERAGE(B1:OFFSET(B1,-4,)))
 
Upvote 0
a better one,
Excel Workbook
ABC
1TimeValueAvg
220:4624
306:2889
414:5754
516:3686
609:396062.6
718:1025
821:0996
912:4041
1016:2072
1105:329064.8
1210:2732
1306:0867
1406:3665
1513:3242
1610:436754.6
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C2=IF(MOD(ROWS(B$2:B2),5)<>0,"",AVERAGE(B2:OFFSET(B2,-4,)))
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top