Oct 05 2020 10:52 AM
Hello Excel community! I am currently taking an indepth Excel course at school and I had points taken off for a question on a test I got wrong. The question was basically what happens when you input =(b1:b4). On my version of Excel, I get the the values in each cell from b1 to b4 entered into respective separate cells as pictured in my screen shot. However, I got the question wrong. When I asked my professor about it, he said that it should have returned a #VALUE! error. On his version of Excel, it does give that error. But on my version, it does not. We compared Excel settings to see if there was anything different, but could not find anything. Do you know what is going on? He is going to give me credit for it, but I would still like to figure out what exactly is going on.
Oct 06 2020 12:08 PM
It seems your professor is ignorant of the latest developments in Excel.
Your version is a subscription of Office 365. Recently Microsoft introduced dynamic arrays into the monthly channel which changed the behavior of array formulas.
Dynamic array formulas and spilled array behavior
Dynamic array formulas vs. legacy CSE array formulas
Dynamic array formulas in non-dynamic aware Excel
In my opinion you should get the full points for the question.
Oct 10 2020 11:07 AM
It looks like lot of professors are still on Excel 2016 since they have a lot of training materials built for this version. That's a headache to generate another set for DA Excel and keep these sets in parallel.