absolute reference

[Thamsanqa]

e.g. =AVERAGE(IF(MOD(ROW($N$2:$N$54),54)=2,$N$2:$N$54)), If I drag it copies down,but the answer remain the same as in the first cell, not calculating the next ROW it use to work before but in the last two days doesn't work maybe something wrong with my excel, I am using 2007

 

[Somendra Misra]

Hi Thamsanqa,

May be you should share your idea what you want to get out of this formula rather than the formula, than may be somebody can help you out.

Regards,

 

[Bhawani]

Function is calculating its value honestly as it should be
1. If you want to calculate 53 cells going down from 53 cells only n2:n54==> n3:n55 then remove $ from function.
2. If you want to calculate 53 cells going down 52, 52, 51 and so on....from n2:n54==> n3:n54 then remove $ from N2 in your function & remaing n54 as it is . i.e. $n$54 which will become n2:$n$54
3. If you want to calculate 53 cells going on increasing 54, 55, 56 and so on....from n2:n54==> n2:n55 then remove $ from N54 in your function & remaing n2 as it is . i.e. $n$2 which will become $n$2:n54.

please post sample file for more on this. hope this will work.

 

[pecoflyer]

Array formula ? Calculations not set to Automatic?

 

[Thamsanqa]

I have uploaded the file, what I am trying to get is the average for Jan to be in Cell J2, Feb Cell J3, Mar cell J4.....if some one can assist.

 

[Bhawani]

PFA. it meets all criteria you want.

PS : You sould calculate month's average below total.

 

[Thamsanqa]

Thanks Bhawani, it works perfectly.......

 

[Bhawani]

Welcome Thamsanga