This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello everyone.
I'm in a lot of confusing....
Although this probably never going to happen in the real scenario, I just curious about its mechanism.
formulas like these below...
I'm fully understand about B column, OFFSET will return ranges (A4:A6)(A5:A7), SUM will sum both ranges, and returns an array {12;19}. But column C seriously confuse me.... I just add a SUM on top of it, ideally, I think it would returns a single value 31(sum of 12+19).
Looks like OFFSET actually returns {A4:A5}{A5:A6}{A6:A7}, how could possibly be like this? it's clearly having {2;3} two values, and 3 is the [height] argument.
Thanks for any response.
Original posted in here: https://answers.microsoft.com/en-us/msoffice/forum/all/could-someone-explain-the-mechanism-of-this-offset/0f4c844c-46ea-4636-a7e5-7759eda11940
@Ezio De Pazzi
I will do some tests on these formulas, if I have some results I would discuss with you.
I test on my environment with Microsoft 365 Apps installed, I get following summary, you could have a look.
The formula "OFFSET(A2,{2,3},0,3)" will return 2 three-dimensional arrays from plane data , each area has 3 dimensions.
The first formula "{=SUM(OFFSET(A2,{2,3},0,3))}" can sum for each three-dimensional array, then return 2 results.
The second formula "{=SUM(SUM(OFFSET(A2,{2,3},0,3)))}" does not seem like the first one, it calculates the data values for each dimension, then return a set of three-dimensional data.
To achieve your needs of formula 2, I would suggest you use "=SUM(SUMIF(OFFSET(A2,{2,3},0,3),"<>"))" formula.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Thanks so much for your response.
I noticed that you're using {2,3} rather than my example {2;3}, but that's ok for the point. Also, SUMIF <>0 is a genius method, currently I'm using SUBTOTAL(9,), but I think SUMIF <>0 is better~
As for the mechanism, actually what I'm trying to figure out is why Excel calculates the data values for EACH dimension, clearly SUM(OFFSET()) returns a 1 two-dimensional array, directions depending on {2,3} or {2;3}. But how the extra layer of SUM decomposes that array and returns a 1 three-dimensional array in this case.
Thanks again mate~
@Emily Hua-MSFT
Hi @Ezio De Pazzi
Yes, the semicolon or comma in the array, this does not affect the use.
> why Excel calculates the data values for EACH dimension,
This action is by design,
> But how the extra layer of SUM decomposes that array and returns a 1 three-dimensional array in this case.
I think it’s because when two SUM formulas are used consecutively with {}, the internal SUM formula will not be calculated as an array formula, it is recognized as “SUM(OFFSET(A2,{2,3},0,3))” without {}.
You could find that as three-dimensional data are placed in one cell, only the top-level data is displayed, and then only 7 is shown on cell.