Using indirect function

chemhany

New Member
Joined
Mar 3, 2021
Messages
22
Office Version
  1. 2019
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 ?? :unsure::unsure:
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,507
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 3, 2021
Messages
22
Office Version
  1. 2019
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,507
Office Version
  1. 365
Platform
  1. Windows
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".
 
Solution

chemhany

New Member
Joined
Mar 3, 2021
Messages
22
Office Version
  1. 2019

ADVERTISEMENT

Thanks
It is working well :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,507
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,144,524
Messages
5,724,839
Members
422,583
Latest member
neomilan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top