I am using Excel 2003 and am trying to average every two rows of data in one column, starting from A1 and ending in A4, for example,
1
2
1
2
I try to do this average on the B1 column/row as follows (based on this info)
=AVERAGE(IF(MOD(ROW(A1:A4),2)=0,A1:A4))
However, when doing the above, B1 returns a 0, even though I intended to get a 1. What would be the best way to make this kind of averaging work?
=AVERAGE(IF(MOD(ROW(A1:A4),2)=0,A1:A4))
The above formula is an array formula. To finalize it you need to press ctrl+shift+enter simultaneously; not just enter. This has also been abbreviated as CSE.
When you do this correctly, excel will wrap the formula in braces (e.g. { and }) like this:
{=AVERAGE(IF(MOD(ROW(A1:A4),2)=0,A1:A4))}
You do not type these braces in yourself; they are added automatically when a formula is entered as an array formula.
-1 after the ROW(A1:A4).The solution to the question was as follows:
{=AVERAGE(IF(MOD(ROW(A1:A4)-1,2)=0,A1:A4))}
where the {} appears after pressing ctrl+shift+enter due to it being an array formula (cannot type that in manually).
The -1 is the offset based on where the data starts, and depending on the modulus operation, since MOD(ROW(A1),2) was 1,
i.e.: if my data instead started on A27, and I instead wanted to average values in every 4 rows, then MOD(ROW(A27),4) would return a 3 and the formula would correspondingly be offset by -3.
You could avoid array formulas using the below:
=IF((INT(ROW()/2)=ROW()/2)=TRUE,AVERAGE((A1,OFFSET(A1,-1,0))),"")
Image:
