# Cellnumber which contains ....

#### DaLiMan

##### Active Member
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?

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

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### fairwinds

##### MrExcel MVP
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

##### Active Member
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

##### MrExcel MVP
DaLiMan said:
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

##### Active Member
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.

Replies
3
Views
183
Replies
0
Views
613
Replies
1
Views
913
Replies
9
Views
313
Replies
1
Views
1K

1,181,614
Messages
5,930,942
Members
436,767
Latest member
Langaws

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back