Using indirect function

chemhany

New Member
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 ??

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

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

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.
Excel Formula:
``=AVERAGE('in(2)'!G13:G56)``
and then in cell B3, enter the formula:
Excel 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".

Thanks
It is working well

You are welcome!

The key is this part of the formula:
Excel 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!

Replies
5
Views
107
Replies
7
Views
240
Replies
1
Views
76
Replies
7
Views
358
Replies
3
Views
258

1,196,506
Messages
6,015,593
Members
441,904
Latest member
edris Alsatouf

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.

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

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