What effect has {1,0} in the IF statement logical_test, within an array function?

Question

Based on this answer, I found out how to make a weighted trendline:

{=LINEST(B2:B7*C2:C7^0.5,IF({1,0},1,A2:A7)*C2:C7^0.5,0)}

But I don't understand how this formula works.

What is Excel doing with IF({1,0},1,x)? What is {1,0}?

Answer 1

It is running the If statement twice for each row in A2:A7, once for 1 and once for 0 - that is, once as true and once as false.

So if range A2:A7 was 1 through 6 your answer would be an array that is 12 digits long and would alternate between the If statement being true, thus giving you a 1 and false, thus giving you the value from A2:A7:

{1,1,1,2,1,3,1,4,1,5,1,6}

The bold 1s are when the If statement was using the 0 part of {1,0} and the non-bold 1 through 6 are when the if statement was using 1 part.

If you walk through it step by step it does the following: (remember, for excel, 1 = true and 0 = false)

if(1,1,A2) 'always 1
if(0,1,A2) 'always A2

if(1,1,A3) 'always 1
if(0,1,A3) 'always A3

if(1,1,A4) 'always 1
if(0,1,A4) 'always A4

If(1,1,A5) 'always 1
If(0,1,A5) 'always A5

If(1,1,A6) 'always 1
If(0,1,A6) 'always A6

{1,A2,1,A3,1,A4,1,A5,1,A6}