Using indirect function

[chemhany]

I have workbook consist of 63 worksheets. They are arranged as following : in-out-in(1)-out(1)-in(2)-out(2) ........... in(31)-out(31)-Average
In average worksheet (the last one), I have 2 tables to calculate data average from each sheet. Table for data from ins worksheets and other one for data from outs worksheets.
How to use both Average and indirect functions to do that ?? What is the formula to be used when drag it down in each table enabled the increment of reference sheet name (in or out) ??
=AVERAGE(In!G13:G56) this is the function which I want to combine with indirect one and when drag it down, in changed to in(1) and so on
Is that possible ??

 

[Joe4]

So for the "ins":
- the first formula should average the range on the "in" sheet
- the second should average the range on the "in(1)" sheet
- the third should average the range on the "in(2)" sheet
- etc

Is that correct?

We can probably make use of the INDIRECT formula in conjunction with ROWS() and COLUMNS(), if you let us know the exact cell addresses the first two formulas are being placed in.

 

[chemhany]

Thanks for your reply and help
Yes, that is correct
For the average from ins sheets, it will be in B2
For outs sheets, it will be in J2
As I mentioned after in sheet, there is out sheet and after in(1) sheets, there is out(1) sheet and so on

 

[Joe4]

OK, since you have different formats (the first one is just "in" while the second is "in(1)"), I would entter the normal formula in cell B2, i.e.

=AVERAGE('in(2)'!G13:G56)

and then in cell B3, enter the formula:

=AVERAGE(INDIRECT("'in("&ROW()-2&")'!G13:G56"))

and copy down for the test of the sheets.

Do the same thing for the "out", just swapping the word "out" for "in".

 

[chemhany]

ADVERTISEMENT

Thanks
It is working well

 

[Joe4]

You are welcome!

The key is this part of the formula:

ROW()-2

What that does is take whatever row the formula is located in, and subtract two off of it.
So, since we are first putting it in cell B3, it evaluates to 3-2=1.
As we copy the formula down the column, the row number returned by ROW() increases by 1 each time, increasing our row reference in the INDIRECT formula by 1 each time.

Hope that makes sense!

 

[chemhany]

Thanks for your illustration