Playing with references

[abinayan]

Hi There,

I've got a question on referencing formulas using the dollar sign.

I have a set of data, for example: 1 2 3 4 5 6 7 8 9
I have to average 4 numbers in one cell. and in the next cell, the next four.
i.e. one cell, =avg(1:4) then in the next cell, =avg(5:8).

I have millions of data like this and I need to know how to reference them in the formula since normally when u drag the formula down it calculate consecutively i.e. one cell=avg(1:4) and next cell, =avg(2:5) etc.

Any help would be greatly appreciated guys.

 

[GlennUK]

Calculate the offset using simple arithmetic, and return a range 4 in length, like:
=AVERAGE(OFFSET($A$1,(ROW()-1)*4+1,0,4,1))

 

[abinayan]

Hi Glenn,

Thank you very much. But can you explain to me how I should set it up? I put the formula in my sheet and changed the $A$1 to the cell where my data in the row starts. It comes up with a Div/0! error.

Your help will be greatly appreciated.

thanks.

 

[GlennUK]

AVERAGE will give a Div/0! error if all the cells are empty. You need to adjust the arithmatic of (ROW()-1)*4+1 if you are starting in a cell that is not in row 1.

 

[abinayan]

Hi Glenn,
I am still not able to manipulate it properly. my data is arranged rowwise not column wise so i adjusted your formula accordingly still didnt work.

My final goal is to get average of data which are in different dates in a month, for the month. (i.e)

<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=832><COLGROUP><COL style="WIDTH: 48pt" span=13 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=20 width=64 align=right>07-Mar</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>14-Mar</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>21-Mar</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>28-Mar</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>04-Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>11-Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>18-Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>25-Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>02-May</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>09-May</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>16-May</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>23-May</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=64 align=right>30-May</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20> 43.4 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 43.4 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 43.4 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 43.8 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 47.5 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 47.2 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 49.2 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 48.0 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 49.8 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 49.8 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 49.8 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 49.8 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> 50.8 </TD></TR></TBODY></TABLE>

and I need the average for march, april and so on.

is there any other way?

Thanks.
I apologise for the inconvinience!

 

[GlennUK]

But you will have some months with 5 weeks, surely?

Are you actually wanting to summarise by month?

 

[abinayan]

Yes! I would love to be able to summarise them monthly. It would be perfect if I could do it with formula rather than a pivot table. since i have a huge amount of data like that which constantly keeps updating on certain points in the month.

Thanks.

 

[GlennUK]

Something like this then:

=SUMPRODUCT(--(MONTH($A$1:$M$1)=month_number)*($A$2:$M$2))/SUMPRODUCT(--(MONTH($A$1:$M$1)=month_number)*--(NOT(ISBLANK($A$2:$M$2))))

... replacing month_number with whatever month number you want to average for. It's taking row 1 ( a1:m1 ) as being the dates, and row 2 as being the corresponding data.

 

[abinayan]

Hi Glenn,

Works perfectly!! is there a way to also include the year in the formula as I have date for a few years worth.

 

[GlennUK]

Add an extra level of testing in the SUMPRODUCT like

*--(YEAR($A$1:$M$1)=year_number)