Please explain the logic behind this issue? Cell range / array question.

%3CLINGO-SUB%20id%3D%22lingo-sub-1301385%22%20slang%3D%22en-US%22%3EPlease%20explain%20the%20logic%20behind%20this%20issue%3F%20Cell%20range%20%2F%20array%20question.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301385%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20just%20learned%20that%20you%20can%20use%20a%20function%20within%20an%20range%20instead%20of%20a%20cell%20reference.%20I%20however%20do%20not%20understand%20the%20logic%20behind%20it%20and%20need%20some%20assistance%20in%20understanding%20it.%20Please%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20mean%2C%20for%20example%2C%20is%20that%20you%20can%20create%20a%20function%20like%20sum(A1%3Aoffset(x%2Cy%2Cz)%2C%20where%20x%2Cy%2Cz%20are%20a%20cell%20reference%2C%20and%20two%20whole%20numbers%2C%20respectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELogically%2C%20this%20seems%20like%20it%20would%20make%20sense%20if%20the%20offset%20function%20returned%20a%20cell%20reference.%20However%2C%20it%20does%20not%3B%20it%20returns%20a%20cell%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20strange%20to%20me%20is%20that%20despite%20the%20above%20SUM%20function%20working%20fine%2C%20when%20you%20input%20a%20cell%20value%20in%20the%20place%20of%20offset%20(even%20if%20it%20is%20the%20same%20value%20that%20the%20offset%20function%20returns)%20then%20the%20SUM%20function%20fails.%20For%20example%2C%20if%20you%20enter%20the%20formula%20sum(A1%3A1000)%2C%20it%20fails.%20But%20if%20your%20OFFSET%20function%20returns%20a%20value%20of%201000%2C%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20someone%20please%20explain%20this%20to%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1301385%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1301663%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20explain%20the%20logic%20behind%20this%20issue%3F%20Cell%20range%20%2F%20array%20question.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301663%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F618714%22%20target%3D%22_blank%22%3E%40nevinkamath%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etake%20a%20long%20look%20(i.e.%2C%20read%20through)%20this%20page%20on%20the%20OFFSET%20function%20and%20see%20if%20it%20explains%20how%20it%20works%20to%20your%20satisfaction%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-offset-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-offset-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312412%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20explain%20the%20logic%20behind%20this%20issue%3F%20Cell%20range%20%2F%20array%20question.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F618714%22%20target%3D%22_blank%22%3E%40nevinkamath%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFew%20functions%20return%20a%20reference%2C%20not%20only%20OFFSET.%20For%20example%2C%20to%20return%20the%20range%20A5%3AA10%20you%20may%20use%20formulas%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DOFFSET(A1%2C4%2C0)%3AOFFSET(A1%2C9%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(1%2CA5%2C0)%3AIF(1%2CA10%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20other%20functions%20which%20could%20return%20references%3A%20OFFSET%2C%20INDEX%2C%20XLOOKUP%2C%20IF%2C%20SINGLE%2C%20CHOOSE%2C%20SWITCH.%20I%20lost%20the%20source%20there%20they%20are%20listed%2C%20you%20may%20google%20for%20them.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20only%20one%20range%20reference%20is%20returned%20by%20formula%2C%20it%20looks%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DA5%3AOFFSET(A1%2C9%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20may%20use%20such%20dynamic%20ranges%20in%20other%20formulas%20same%20way%20as%20fixed%20one%2C%20e.g.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUM(A5%3AOFFSET(A1%2C9%2C0))%0A%0Ais%20equivalent%20of%0A%0A%3DSUM(A5%3AA10)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUM(A5%3A10)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eis%20just%20wrong%20syntax.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there,

 

I've just learned that you can use a function within an range instead of a cell reference. I however do not understand the logic behind it and need some assistance in understanding it. Please help!

 

What I mean, for example, is that you can create a function like sum(A1:offset(x,y,z), where x,y,z are a cell reference, and two whole numbers, respectively.

 

Logically, this seems like it would make sense if the offset function returned a cell reference. However, it does not; it returns a cell value. 

 

What is strange to me is that despite the above SUM function working fine, when you input a cell value in the place of offset (even if it is the same value that the offset function returns) then the SUM function fails. For example, if you enter the formula sum(A1:1000), it fails. But if your OFFSET function returns a value of 1000, it works.

 

Could someone please explain this to me?

 

Thanks a lot.

2 Replies
Highlighted

@nevinkamath 

 

take a long look (i.e., read through) this page on the OFFSET function and see if it explains how it works to your satisfaction:

https://exceljet.net/excel-functions/excel-offset-function

 

Highlighted

@nevinkamath 

Few functions return a reference, not only OFFSET. For example, to return the range A5:A10 you may use formulas like

=OFFSET(A1,4,0):OFFSET(A1,9,0)

or

=IF(1,A5,0):IF(1,A10,0)

or other functions which could return references: OFFSET, INDEX, XLOOKUP, IF, SINGLE, CHOOSE, SWITCH. I lost the source there they are listed, you may google for them.

 

If only one range reference is returned by formula, it looks like

=A5:OFFSET(A1,9,0)

You may use such dynamic ranges in other formulas same way as fixed one, e.g.

=SUM(A5:OFFSET(A1,9,0))

is equivalent of

=SUM(A5:A10)

but

=SUM(A5:10)

is just wrong syntax.