Cannot use the COLUMN() function in an array?

[billj]

Hi sir,

Can I ask why the array formula below failed to work?

=PRODUCT(1+OFFSET($A$1,0,0,COLUMN($C$1)-COLUMN($A$1),1))

It worked nicely if I replace COLUMN($C$1) with 3, and COLUMN($A$1) with 1.

[XOR LX]

Hi,

When array-entered, Excel is expecting an array to be returned from the COLUMN functions. This conflicts with the single value (e.g. 2 - effectively {2} here) you are returning. You could first coerce this into a scalar quantity before passing to the OFFSET:

=PRODUCT(1+OFFSET($A$1,0,0,SUM(COLUMN($C$1)-COLUMN($A$1)),1))

though it is more than likely that there will be a better solution. Can I ask you why the two COLUMN functions? Particularly since they are absolute references, so I can't imagine you, for example, dragging this formula to further cells?

Regards

[billj]

HI XOR LX,

Thanks for the great advice and help. I tried to use some 'relative' position for the calculation. This is an example.

I will later change $C$1 to COLUMN() so that the position can be updated for difference cell locations.

Good knowledge to know the behavior of the COLUMN() function. Thank you!

Hi,

When array-entered, Excel is expecting an array to be returned from the COLUMN functions. This conflicts with the single value (e.g. 2 - effectively {2} here) you are returning. You could first coerce this into a scalar quantity before passing to the OFFSET:

=PRODUCT(1+OFFSET($A$1,0,0,SUM(COLUMN($C$1)-COLUMN($A$1)),1))

though it is more than likely that there will be a better solution. Can I ask you why the two COLUMN functions? Particularly since they are absolute references, so I can't imagine you, for example, dragging this formula to further cells?

Regards

[XOR LX]

You're welcome.