How to create an indirect range reference

%3CLINGO-SUB%20id%3D%22lingo-sub-987893%22%20slang%3D%22en-US%22%3EHow%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-987893%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20create%20an%20indirect%20reference%20to%20a%20range%20for%20an%20average()%20or%20Max()%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20I%20want%20to%20display%20the%20average%20value%20in%20a%20part%20of%20a%20column%2C%20but%20the%20part%20is%20changeable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EToday%20I%20want%20to%20show%20the%20average(B4%3AB11)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20next%20week%20it%20might%20be%20average(B4%3AB12)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20could%20have%20a%20cell%20G1%20containing%204%3C%2FP%3E%3CP%3Eand%20cell%20G2%20containing%2011%3C%2FP%3E%3CP%3EThen%20a%20formula%20like%26nbsp%3B%3DAVERAGE(%22B%22%26amp%3BG1%3A%22B%22%26amp%3BG2)%20would%20allow%20me%20to%20change%20the%20values%20in%20G1%20and%20G2%20to%20calculate%20the%20average%20for%20different%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20possible%2C%20isn't%20it%3F%3C%2FP%3E%3CP%3EHow%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-987893%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-987915%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-987915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404960%22%20target%3D%22_blank%22%3E%40Feargal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DAVERAGE(OFFSET(INDIRECT(%22B%22%26amp%3BG1)%2C%2C%2CG2-G1%2B1))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-991415%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-991415%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20works%3C%2FP%3E%3CP%3ECan%20you%20explain%20what%20it%20is%20doing%3F%3C%2FP%3E%3CP%3EI%20understand%20the%20average%20bit%2C%20but%20not%20the%20use%20of%20offset%20and%20indirect%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-992064%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-992064%22%20slang%3D%22en-US%22%3EOFFSET%20and%20INDIRECT%20are%20volatile.%20I%20prefer%20the%20non-volatile%20INDEX%2C%20like%20this%3A%3CBR%20%2F%3E%3DAVERAGE(INDEX(B%3AB%2CG1)%3AINDEX(B%3AB%2CG2))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-992193%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-992193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404960%22%20target%3D%22_blank%22%3E%40Feargal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20your%20data%20arrangement%2C%20you%20may%20might%20arrive%20at%20your%20goal%20by%20tabling%20the%20data%20and%20using%20the%20totals%20row%20feature.%26nbsp%3B%20You%20can%20toggle%20the%20total%20row%20on%2Foff%20as%20needed%20when%20adding%20more%20data.%20It%20will%20always%20be%20at%20the%20bottom.%26nbsp%3B%20This%20avoids%20volatile%20functions%20and%20references%20to%20entire%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-994608%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-994608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhats%20the%20difference%20between%20volatile%20and%20non-volatile%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-994616%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-994616%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20448px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155809iD4D46F7D44A8467E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screenshot%202019-11-08%20at%2009.32.39.png%22%20title%3D%22Screenshot%202019-11-08%20at%2009.32.39.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-994938%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-994938%22%20slang%3D%22en-US%22%3EVolatile%20functions%20are%20discussed%20in%20this%20link%3A%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Fwww.decisionmodels.com%2Fcalcsecretsi.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.decisionmodels.com%2Fcalcsecretsi.htm%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-994946%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-994946%22%20slang%3D%22en-US%22%3EMerged%20cells%20in%20Column%20B%20might%20be%20causing%20the%20error.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1010951%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20an%20indirect%20range%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1010951%22%20slang%3D%22en-US%22%3ENo%20merged%20cells%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

How do I create an indirect reference to a range for an average() or Max() function?

 

So if I want to display the average value in a part of a column, but the part is changeable.

 

Today I want to show the average(B4:B11)

 

But next week it might be average(B4:B12)

 

If I could have a cell G1 containing 4

and cell G2 containing 11

Then a formula like =AVERAGE("B"&G1:"B"&G2) would allow me to change the values in G1 and G2 to calculate the average for different ranges.

 

That is possible, isn't it?

How?

 

TIA

11 Replies
Highlighted

@Feargal 

You may try something like this...

 

=AVERAGE(OFFSET(INDIRECT("B"&G1),,,G2-G1+1))

Highlighted

That works

Can you explain what it is doing?

I understand the average bit, but not the use of offset and indirect

Highlighted
OFFSET and INDIRECT are volatile. I prefer the non-volatile INDEX, like this:
=AVERAGE(INDEX(B:B,G1):INDEX(B:B,G2))
Highlighted

@Feargal 

Depending on your data arrangement, you may might arrive at your goal by tabling the data and using the totals row feature.  You can toggle the total row on/off as needed when adding more data. It will always be at the bottom.  This avoids volatile functions and references to entire columns.

Highlighted

@Twifoo 

Whats the difference between volatile and non-volatile?

 

Highlighted
Highlighted
Volatile functions are discussed in this link:
http://www.decisionmodels.com/calcsecretsi.htm
Highlighted
Merged cells in Column B might be causing the error.
Highlighted

@Twifoo 

As that article seems to suggest, Volatile functions recalculate on every change, whereas involatile do not. 

 

Am I getting that correct?

 

If so then I definitely want a volatile function. This is a very small dataset and recalc issues are irrelevant

Highlighted
No merged cells
Highlighted
The choice is yours.