can I do this w/out VBA

[bobf]

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.

[Miguel Zapico]

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
>
>

[Richard]

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

[Gary''s Student]

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
>
>

[bobf]

I'll give your suggestions a try!

[Richard]

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