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