Formula Changing

3 replies to this topic

### #1 JPK1066

JPK1066

• Members
• 101 posts

Posted 08 November 2017 - 09:51 PM

I will attach a WS with a cross WS reference to a game counter on each of my 30 WS's, cell a94. Column A contains those dynamic count numbers. I would like to make my formulas in columns F and H dependent on the game count number. Column F is drawing data from column J on the WS's, column H is drawing data from column M on the WS's.

With a game count of 10 on row 4 for Oklahoma column F is targeting J4:J12 and column H is targeting M4:M12

As you'll see the Oklahoma formula will be:

=average('Oklahoma 17-18'!J3:J13)

If I could replace the range address J3:J13 with a formula such as:

J(A4-B5):J(A4+C5)

I could put any number that I want in B4 and C4 and create a dynamic range spread, meaning it could be acting on any desired number of cells. As an example if I wanted to act on the last 2 cells for each team I would have A4-(1 in B4)  and (0 in C4)

If I wanted to act on 6 cells ............................I would have A4-(4 in B4) and (+1 in C4)

I would change the spread numbers in columns B and C and auto fill down and be able to change the target range for all my computations in short order.

You have instructed me in the past in using formulas for cell references but I can't come up with this one.

TY

JPK1066

### #2 Jonathan

Jonathan

• 887 posts

Posted 10 November 2017 - 10:19 PM

Hi JPK,

This is a case where INDIRECT is what you are looking for. In your example you ask for:

=AVERAGE(J(A4-B5):J(A4+C5))

You could achieve this using the following INDIRECT formula:

=AVERAGE(INDIRECT("J" & A4-B5 & ":J" & A4+C5))

I can't apply this to your example workbook as the data is coming from an external workbook, but you should be able to use this to achieve the result you need.

You can see more about how to use the INDIRECT function in the video lesson: Expert Skills Lesson 4-5 Use intersection range names and the INDIRECT function.

I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

### #3 JPK1066

JPK1066

• Members
• 101 posts

Posted 11 November 2017 - 11:54 PM

Please review the WS that I'm sending as I apparently need more help executing my strategy.

As always TY for you time and knowledge,

JPK1066

### #4 Jonathan

Jonathan

• 887 posts

Posted 12 November 2017 - 10:14 PM

Hi JPK,

I've taken a look at your attachment and found the following question:

I have inserted the INDIRECT function and replaced the cell reference J3:J13 with the above formula.
That is:
("J" & A4-B4 & ":J" & A4+C4)
You can see that this should produce an average of 3 cells in each one of the WS's as 12-0 is 12 and 12 + 2 is 14 which should capture 3 cells on each WS.
When I apllied this formula to the first team Atlanta, Excel asked if this was not a formula? It left the first "J" highlighted in the cell address.
I elected to copy the data to the left without formulas to avoid the long address prefix.
This formula is addressing WS's in the same WB as it should.
I'm assuming that I have correctly inserted the WS address of 'Atlanta 17-18'!
I would ask you to concur in theory that with column A dynamically changing with the Countif function importing the number of games played that the numbers in column B and C would inturn direct the formulas to act on the number of cells between.
If you could explain why the Indirect function should allow the forumlas work I would appreciate it.
Hopefully You will be able to discern an error in my interpretation and formula construction.

=Average(INDIRECT('Atlanta 17-18'!("J" & A4-B4 & ":J" A4+C4)))

This is not the correct syntax.

It looks like you want the formula to ultimately simplify down to:

=AVERAGE('Atlanta 17-18'!J12:J14)

It might be better to focus on first producing the text 'Atlanta 17-18'!J12:J14 and then applying the INDIRECT and AVERAGE functions.

To create the text, you can use the following formula:

="'Atlanta 17-18'!J" & A4-B4 & ":J" & A4+C4

You can see more about how this formula works in the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

This is a cross-worksheet formula, which you can see more about in: Essential Skills Lesson 6-6 Create cross worksheet formulas.

Now that you have this text, you can use the INDIRECT function to make Excel treat it as a cell reference instead of text:

=INDIRECT("'Atlanta 17-18'!J" & A4-B4 & ":J" & A4+C4)

The use of the INDIRECT function is explained in the video tutorial: Expert Skills Lesson 4-5 Use intersection range names and the INDIRECT function.

...and finally add the AVERAGE function to calculate the average of the values:

=AVERAGE(INDIRECT("'Atlanta 17-18'!J" & A4-B4 & ":J" & A4+C4))

AVERAGE and other similar functions are covered in: Essential Skills Lesson 2-15 Use AutoFill to adjust formulas.

I hope this helps and you're now able to create the formula you need.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users