Home

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
Feargal
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

@Feargal 

You may try something like this...

 

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

That works

Can you explain what it is doing?

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

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

@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.

@Twifoo 

Whats the difference between volatile and non-volatile?

 

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

@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

No merged cells
The choice is yours.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies