Excel Dynamic Arrays - possible bug?

Question

I have 5 numbers entered in cells B1:F1. If I write the following formula:

= MIN( OFFSET( A1, 0, {1,3,5} ) )

then it correctly returns the smallest of the numbers in cells B1, D1, F1. However, if I get rid of the MIN() and make the formula:

= OFFSET( A1, 0, {1,3,5} )

I would expect it to return the 3 numbers as a dynamic array. It does return a dynamic array sized correctly, but in each of the 3 cells it gives me #VALUE!. That would seem to be a bug - and I know dynamic arrays are relatively new - but would anybody have any thoughts to the contrary? Thanks.

BigBen · Accepted Answer · 2021-06-08 01:30:20Z

4

It's probably better to just use INDEX as it's not volatile.

=INDEX(B1:F1,,{1,3,5})

EDIT:

For a more general version of this, consider:

=INDEX(1:1,,{2, 4, 6})

or

=INDEX(1:1,,{1, 3, 5}+1)

edited Jun 8, 2021 at 1:30

answered Jun 7, 2021 at 19:20

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chris neilsen Over a year ago

Good answer. A more general soultion: =INDEX(1:1,,{2, 4, 6}) or =INDEX(1:1,,{1, 3, 5}+1)

BigBen Over a year ago

@chrisneilsen - yep, that's good detail, will add to the body of the question.

COG · Accepted Answer · 2021-06-07 19:23:55Z

0

Never mind! It now works, but I didn't change anything. It must be caused somehow by the fact that OFFSET is a volatile function...

answered Jun 7, 2021 at 19:23

COG

3212 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel Dynamic Arrays - possible bug?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related