Hmm. Every paragraph is either completely wrong or contains an error. (One is trivial though ;-) ) 1) Completely wrong. =(A2:A7>0.5) does not return an array. You can see this for yourself by using the "Evaluate Formula" tool. 2) The single value result will be either FALSE, TRUE, the corresponding cell error, or #VALUE!. See my answer for the why. 3) Ditto, with 0 and 1 instead of FALSE and TRUE. …

… 4) I lied ;-) This paragraph is technically correct, if you assume that by "array" you mean A2:A7 and not the result of --(A2:A7>0.5). (And yes, I'm aware of, and ignoring the missing ) typo ;-) ) 5) Technically, this should read "… if the array contains any errors, then the formula will return the first error." (Ditto for the typo.)

@robinCTS You need to better understand some of the limitations of Excel. =(A2:A7>0.5) certainly does return an array. However, if you enter it as a non-array formula, the Evaluate Formula tool will not show it. However, you can easily confirm the presence of an array by placing your cursor in the formula bar and selecting F9. You can also return the individual elements with a formula such as =INDEX(A2:A7>0.5,5,1) entered normally.

@robinCTS the other paragraphs of my answer are also correct.

@robinCTS and, as you point out in your response, you can also coerce an array in the Evaluate Formula tool by wrapping with the INDEX function, or even the SUMPRODUCT

They way I understood it, pressing F9 doesn't accurately reflect what actually happens in the cell. It always treats operations on multi-cell ranges as array-based and it doesn't calculate circular references the same way. Do you have a source that says otherwise?

There are many references about using F9 in edit mode. From the first google result In Microsoft Excel, F9 key is an easy and quick way to check and debug formulas. It lets you evaluate only the selected part of the formula by replacing it with the actual values that part operates on, or with the calculated result.

@robinCTS I was not able to find a reference as to why there is a difference between F9 in edit mode, and the Formula Evaluate tool, with regard to how arrays are evaluated. The way I understood it, it had to do with what was actually in the formula (F9) vs what gets returned to the cell in question.

Normally entered formulas used as a criteria for conditional formatting will also return different results. eg. =OR(A2:A7>0.5) normally entered.

Same here. I found references to differences, e.g. stackoverflow.com/q/36908089 but nothing yet explaining what/why.

It seems to me that if the a2:a7< 0.5 did not "return" an array, there'd be no way of getting the elements of that array out of it. I suppose that depends on what "return" means. Clearly it does not return it to the cell, no matter how entered, but requires some function to operate on the array.

Also the fact that placing =(A2:A7>0.5) in B1 results in #VALUE! while pressing F9 shows {1;1;1;1;0;0} proves that it does things differently. If that formula returns an array in the cell, where does the error come from?

Gotta run now -- continue later?

Sure. I've just found proof for my case!

I'm off to bed now. Will post proof when I get up.