question

PowerDekor-2055 avatar image
0 Votes"
PowerDekor-2055 asked emilyhua-msft edited

Could someone explain the mechanism of this OFFSET formula? Thanks

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...
560d634a-66c8-447a-9b6a-db101a991cc9

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


office-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PowerDekor-2055
I will do some tests on these formulas, if I have some results I would discuss with you.

0 Votes 0 ·

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft edited

@PowerDekor-2055

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.
146639-1.gif

The first formula "{=SUM(OFFSET(A2,{2,3},0,3))}" can sum for each three-dimensional array, then return 2 results.
146793-capture25.jpg

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.
146785-capture26.jpg

To achieve your needs of formula 2, I would suggest you use "=SUM(SUMIF(OFFSET(A2,{2,3},0,3),"<>"))" formula.
146759-capture27.jpg


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.





1.gif (66.0 KiB)
capture25.jpg (34.8 KiB)
capture26.jpg (35.0 KiB)
capture27.jpg (26.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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~
@emilyhua-msft

0 Votes 0 ·

Hi @PowerDekor-2055
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 {}.
147371-capture30.jpg
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.

1 Vote 1 ·
capture30.jpg (26.7 KiB)