Question about dynamic arrays

[TomCon]

I have Excel 365 and it is upgraded to current so i believe i have dynamic arrays.
Two questions about arrays and dynamic arrays.
(1)
This formula only works if entered with Ctl-Shift-Enter, the old way.
=SUM(IF(goal!CN1551:CN1685=goal!CO1551:CO1685,1,0))
If i enter it without Ctl-Shift-Enter it gives #VALUE, but if entered with Ctl-Shift-Enter it gives the correct answer.
Shouldn't it simply work as a dynamic array without Ctl-Shift-Enter?
(2)
A slight modification. This formula also works [only] with Ctl-Shift-Enter and gives the correct answer.
=SUM(IF(OFFSET(goal!$A$1,1550,91,1685-1551+1,1)=goal!CO1551:CO1685,1,0))
But, if i change the column number (91) argument to COLUMN($CN$1)-1 [which evaluates to 91], it now gives #VALUE in all cases, whether entered with Ctl-Shift-Enter or not.
=SUM(IF(OFFSET(goal!$A$1,1550,COLUMN(goal!$CN$1)-1,1685-1551+1,1)=goal!CO1551:CO1685,1,0))}
Gives #VALUE. Why can one not put the expression COLUMN($CN$1) in the formula as an argument? Is there a workaround?

Thank you!

 

[StephenCrump]

I have Excel 365 and it is upgraded to current so i believe i have dynamic arrays.

I understand not all Excel365 users have access yet to dynamic arrays. Your description suggests that you do not.

If you do this, i.e. enter the single formula B1:=A1:A5, what do you see?

- A dynamic array spilling down over B2:B5, as pictured here?
- Or just a single value 1 displaying in B1? (But B1 actually holding the array {1;2;3;4;5})?

	A	B
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=A1:A5
Dynamic array formulas.

 

[Fluff]

If you are on the semi-annual channel, you wont get the dynamic arrays until about July.

 

[Peter_SSs]

I have Excel 365

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version & (platform) you are using. (Don’t forget to scroll down & ‘Save’)

 

[TomCon]

I understand not all Excel365 users have access yet to dynamic arrays. Your description suggests that you do not.

If you do this, i.e. enter the single formula B1:=A1:A5, what do you see?

- A dynamic array spilling down over B2:B5, as pictured here?
- Or just a single value 1 displaying in B1? (But B1 actually holding the array {1;2;3;4;5})?

	A	B
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=A1:A5
Dynamic array formulas.

I see the single value. So, i guess i do not have dynamic arrays. I am very confused and wonder if i could "imagine" the following but i swear that earlier I had observed that i did have the behavior of dynamic arrays. Maybe i am mistaken about that. Have there been any cases of regression back to non-dynamic arrays? Thanks!

 

[TomCon]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version & (platform) you are using. (Don’t forget to scroll down & ‘Save’)

thank you. Done!

 

[Fluff]

What channel are you on?

 

[TomCon]

If you are on the semi-annual channel, you wont get the dynamic arrays until about July.

Can you tell me how to tell what distribution channel i am on. Looking at "account" in Excel i do not see anything that indicates that.

 

[Peter_SSs]

Looking at "account" in Excel i do not see anything that indicates that.

That is the place to look.