Cellnumber which contains ....

[DaLiMan]

Hi there,

I my Excel file which contains a pivot table I have this formula which calculates the average in the last (grand total) table.

The formula works for now, but tommorow or every other day this cell can be moved to say "F" or "G" or whatever.

Is there a way to let excel determine in which colomn the (grand total) stands (D,E,F,G,....etc) and use this in my average formula?

=AVERAGE(INDIRECT("E5:E"&(4+COUNT(A:A))))

 

[fairwinds]

Hi,

I do not unerstand why you do not handle this within the pivot table but...


Try:

=AVERAGE(OFFSET(E5,,MATCH("Grand Total",E2:L2,0)-1,COUNT(A:A)))
entered with Ctrl + shift + enter.

You may need to change "E2:L2" to the approriate row.

 

[DaLiMan]

Thanx for the reply and nifty formula.

Can E5 also be matched, because when I use this formula I think it use E5 as the first item for the average no matter what.

I tried to change it a bit but without luck a must add.
=AVERAGE(OFFSET(E5,,MATCH("Grand Total",E4:L4,0)-1,COUNT(A:A)))

I tried
=AVERAGE(OFFSET(MATCH("Grand Total",E4:L4,0)-1,,MATCH("Grand Total",E4:L4,0)-1,COUNT(A:A)))

PS: What does [Ctrl + shift + enter] do?

 

[fairwinds]

I think it use E5 as the first item for the average no matter what.

No it does not. The match formula will tell how may columns from E5 the range will be offset. The range will be only one column wide. The number of rows will be determend as you did before.

Ctrl + shift + enter is not needed , my mistake, sorry.

 

[DaLiMan]

OK, I get it.

Had to change the E into C and then it works from colomn B to whatever.

Many thanx !!!
Works really great.