I won't go into details on why this is set up this...just know I can not
change it! I have a 'report' sheet with 20 coulmns explicitly
referencing a 'data' sheet. At any given point up to 20 columns on the
'data' sheet may be populated. The last column on the 'data' sheet is
ALWAYS a grand total with a column header labelled 'GrandTotal'.

If there are less than 20 columns of data on the referenced 'data'
sheet then the extraneous columns on the 'report' sheet simply contain
references to data that is not present and is ignored. In general then
the 'report' sheet Grand Total column can float left or right dependng
on when the run is run and how many columns are populated on the
'data'. Wheeeeew! Now the question.

They want me to add an average column. The average should be Column A
through the column before the Grand Total; which could vary depending
on how many columns are present on the 'data' sheet. I was thinking I
could add a column labelled: 'Average' as column 21 on the 'report'
sheet since I know there is NEVER any data there. I am stuck though
as to how to say:

=AVERAGE(A1: the column just before the grand total column)

My thoughts were 1) I need to determine the letter of the column where
the Grand Total column is and subtract one. 2) I need to then somehow
reference this in the AVERAGE function.

I am unsure how to do this or how to even format the AVERAGE function
if I can do so. I hope this makes sense because if I can't automate it
I m ust do it manually.


--
bobf
------------------------------------------------------------------------
bobf's Profile: http://www.excelforum.com/member.php...o&userid=34094
View this thread: http://www.excelforum.com/showthread...hreadid=540240

You can use a formula like this on your column 21:
=AVERAGE(OFFSET(A1,,,1,COUNTA(A1:T1)))

Miguel.

"bobf" wrote:


I won't go into details on why this is set up this...just know I can not
change it! I have a 'report' sheet with 20 coulmns explicitly
referencing a 'data' sheet. At any given point up to 20 columns on the
'data' sheet may be populated. The last column on the 'data' sheet is
ALWAYS a grand total with a column header labelled 'GrandTotal'.

If there are less than 20 columns of data on the referenced 'data'
sheet then the extraneous columns on the 'report' sheet simply contain
references to data that is not present and is ignored. In general then
the 'report' sheet Grand Total column can float left or right dependng
on when the run is run and how many columns are populated on the
'data'. Wheeeeew! Now the question.

They want me to add an average column. The average should be Column A
through the column before the Grand Total; which could vary depending
on how many columns are present on the 'data' sheet. I was thinking I
could add a column labelled: 'Average' as column 21 on the 'report'
sheet since I know there is NEVER any data there. I am stuck though
as to how to say:

=AVERAGE(A1: the column just before the grand total column)

My thoughts were 1) I need to determine the letter of the column where
the Grand Total column is and subtract one. 2) I need to then somehow
reference this in the AVERAGE function.

I am unsure how to do this or how to even format the AVERAGE function
if I can do so. I hope this makes sense because if I can't automate it
I m ust do it manually.


--
bobf
------------------------------------------------------------------------
bobf's Profile: http://www.excelforum.com/member.php...o&userid=34094
View this thread: http://www.excelforum.com/showthread...hreadid=540240

One way would be to use the Indirect function

In the example below A1:T1 are 20 numbers, U1 is the Grand total of the
numbers and the formula below is in V1

=AVERAGE(INDIRECT("A1:"&ADDRESS(ROW(),COLUMN($U1)-1)))

The last bit with the $U will float about and always give the cell
address of the column immediately before the U total column (or its
equivalent where there are less than 20 columns).

HTH

Hi Richard:

Is there any way to make the A1 adjust as the formula is copy'd down??
--
Gary's Student


"Richard" wrote:

One way would be to use the Indirect function

In the example below A1:T1 are 20 numbers, U1 is the Grand total of the
numbers and the formula below is in V1

=AVERAGE(INDIRECT("A1:"&ADDRESS(ROW(),COLUMN($U1)-1)))

The last bit with the $U will float about and always give the cell
address of the column immediately before the U total column (or its
equivalent where there are less than 20 columns).

HTH

I'll give your suggestions a try!


--
bobf
------------------------------------------------------------------------
bobf's Profile: http://www.excelforum.com/member.php...o&userid=34094
View this thread: http://www.excelforum.com/showthread...hreadid=540240

Yes indeed.
I realised after I posted it that a generalised solution would have
been better so that it doesn't matter which column or which row the
data starts in.

Put the column letter of the first column of data in a cell somewhere
and name it "First" . In this example it should contain the letter A,
but if the data starts in another column just change the column letters

Then in V1 ( or anywhere else on row 1) put

=AVERAGE(INDIRECT(First&ROW()&":"&ADDRESS(ROW(U1), COLUMN(U1)-1)))

and copy it down