Cellnumber which contains ....

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
Joined
Jun 1, 2004
Messages
295
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
Joined
May 15, 2003
Messages
8,638
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
Joined
Jun 1, 2004
Messages
295
OK, I get it.

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

Many thanx !!! (y)
Works really great. :biggrin: :biggrin:
 

Forum statistics

Threads
1,147,691
Messages
5,742,659
Members
423,746
Latest member
Joaogomes

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top