Indirect Issue

[ruperupe]

Hi all,

I have a problem where im trying to use indirect in a formula.

{=SUM(IF((MONTH(Data!$A$4:$A$968)=C$2)*(YEAR(Data!$A$4:$A$968)=C$3),Data!$C$4:$C$968))/SUMPRODUCT(IF((MONTH(Data!$A$4:$A$968)=C$2)*(YEAR(Data!$A$4:$A$968)=C$3),Data!$C$4:$C$968<>0)*1)}

This is copying across the page well but I am having to adjust the formula a lot to respresent new columns

for example I want to adjust the C to an E - I have tried using indirect to keep the E in lets say B5

Ive tried to use

{=SUM(IF((MONTH(Data!$A$4:$A$968)=C$2)*(YEAR(Data!$A$4:$A$968)=C$3),Data!INDIRECT"$&B5&"$4:INDIRECT"$&B5&"$968))/SUMPRODUCT(IF((MONTH(Data!$A$4:$A$968)=C$2)*(YEAR(Data!$A$4:$A$968)=C$3),Data!$C$4:$C$968<>0)*1)}

I know this is an issue with the parenthases but im not sure why, please can you help me out with this..

Thanks Rup

[Haseeb Avarakkan]

Hi,

In B5 enter a column # instead of letter. eg: 3 for column C, 4 for column D, 5 for column E etc.... then use INDEX rather than INDIRECT which is a non volatile.

=AVERAGE(IF(MONTH(Data!$A$4:$A$968)=C$2,IF(YEAR(Data!$A$4:$A$968)=C$3,IF(Data!$C$4:$C$968<>0,INDEX(Data!$4:$968,0,$B$5)))))

with CTRL+SHIFT+ENTER.

Since you are on XL 2007, you can use AVERAGEIFS. In C2 enter a date which you want to calculate avg. The below formula will calculate Avg in the full month entered in C2,

=AVERAGEIFS(INDEX(Data!$4:$968,0,$B$5),Data!$A$4:$A$968,">"&EOMONTH(C$2,-1),Data!$A$4:$A$968,"<="&EOMONTH(C$2,0),Data!$C$4:$C$968,"<>0")

with just ENTER

[ruperupe]

This is great thanks for your help.