Introduction
Many Excel users do not really like the OFFSET() function but I think that is there problem not ours! This page shows two things:
- how to use OFFSET() when it addresses a range that uses Dynamic Arrays
- how to use OFFSET() with it does not address a range that uses Dynamic Arrays
In the following screenshot you can see the first table and the OFFSET() range that uses Dynamic Arrays AND the SPILL Range Operator
The formula in cell L16 is =OFFSET(E16#,0,-1,M14,M13) and E16# is the SPILL Range Operator. The operator works because the range E16:J16 is created with this Dynamic Array formula =SEQUENCE(1,E13,C5,10000).
Notice the OFFSET() table size is determined by the cells M14 and M13, which in turn are driven by cells E13 and E12. Try to work out why, eg, E13 says 3 yet M13 says 4.
The second range, using ordinary, non Dynamic Array functions, gives exactly the same answer and yet this version of the OFFSET() function uses the reference of E29 rather than the reference E15# used by the first OFFSET() example.
In the end, it doesn't really matter for this example which version of the table layout we use since OFFSET() is essentially the same in each case!
Download: my file
Duncan Williamson
4th September 2020
No comments:
Post a Comment