Offset - Setting a dynamic range

[Krugas]

Hello,

I'm learning some stuff about excel and I came across the usage of the Offset function to some specific data into dynamic data. So the function example is this one: OFFSET(DynamicRangeSum!$C$5;0;0;1;COUNTA(DynamicRangeSum!$C$5:$M$5)).
How can this function get the values from a dynamic range if we're limiting its range to C5:M5?

[Norie]

Why can't a dynamic range be limited to a certain range?

[Pepe Le Mokko]

As COUNTA counts the number of non-empty cells, the width of the range might vary from 1 to 11 depending on how many non-empty cells there are in the range - So, it is dynamic

FYI as OFFSET is volatile, using INDEX is a more stable alternative

[Krugas]

So imagining the situation that we've a range of data in C5:H5 and we name it RevenueData. And then there's in another cell the formula =SUM(RevenueData), the author I'm reading claims that if you insert data on I5 this SUM wouldn't be able to capture it (until here understood), but with that OFFSET it would capture everything.
But if I'm doing this OFFSET from C5:M5 why not just do SUM(C5:M5)? Or name RevenueData to C5:M5 and then just do SUM(RevenueData)? What's the advantage of using the Offset in here?

[Tony Valko]

A dynamic range is used when you will be adding data to the end of the data range so there is not a specific end of range. You may not know how much data you'll be adding and sometimes, using an arbitrary end of range is not efficient depending on what type of functions you use to reference the range.

Some functions ignore empty cells while others do not. So, you may want to use a dynamic range so any calculations aren't being done on empty unused cells.

[Pepe Le Mokko]

Again, the introduction of the ;COUNTA(DynamicRangeSum!$C$5:$M$5) will limit the length of the range to the number of non-empty cells between C and M
So If there are three cells containing something, the SUM will sum range C5:E5. Why one would want to do this is not quite clear
To see your formula at work, use the Evaluate formula tool

[Tony Valko]

For some reason this thread does not appear in my Subscribed Threads folder.

So, I'm posting this reply to see if it gets updated (so I can follow the conversation).

EDIT: The Subscribed Threads folder has updated.