Different Results between Professor's Excel and My Excel.

%3CLINGO-SUB%20id%3D%22lingo-sub-1746609%22%20slang%3D%22en-US%22%3EDifferent%20Results%20between%20Professor's%20Excel%20and%20My%20Excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1746609%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20community!%20I%20am%20currently%20taking%20an%20indepth%20Excel%20course%20at%20school%20and%20I%20had%20points%20taken%20off%20for%20a%20question%20on%20a%20test%20I%20got%20wrong.%20The%20question%20was%20basically%20what%20happens%20when%20you%20input%20%3D(b1%3Ab4).%20On%20my%20version%20of%20Excel%2C%20I%20get%20the%20the%20values%20in%20each%20cell%20from%20b1%20to%20b4%20entered%20into%20respective%20separate%20cells%20as%20pictured%20in%20my%20screen%20shot.%20However%2C%20I%20got%20the%20question%20wrong.%20When%20I%20asked%20my%20professor%20about%20it%2C%20he%20said%20that%20it%20should%20have%20returned%20a%20%23VALUE!%20error.%20On%20his%20version%20of%20Excel%2C%20it%20does%20give%20that%20error.%20But%20on%20my%20version%2C%20it%20does%20not.%20We%20compared%20Excel%20settings%20to%20see%20if%20there%20was%20anything%20different%2C%20but%20could%20not%20find%20anything.%20Do%20you%20know%20what%20is%20going%20on%3F%20He%20is%20going%20to%20give%20me%20credit%20for%20it%2C%20but%20I%20would%20still%20like%20to%20figure%20out%20what%20exactly%20is%20going%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excelhelp.JPG%22%20style%3D%22width%3A%20877px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F224240i8F5AB32031EC8715%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excelhelp.JPG%22%20alt%3D%22excelhelp.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1746609%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1750818%22%20slang%3D%22en-US%22%3ERe%3A%20Different%20Results%20between%20Professor's%20Excel%20and%20My%20Excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1750818%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820806%22%20target%3D%22_blank%22%3E%40AuchnotOuch%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20your%20professor%20is%20ignorant%20of%20the%20latest%20developments%20in%20Excel.%3C%2FP%3E%3CP%3EYour%20version%20is%20a%20subscription%20of%20Office%20365.%20Recently%20Microsoft%20introduced%20dynamic%20arrays%20into%20the%20monthly%20channel%20which%20changed%20the%20behavior%20of%20array%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDynamic%20array%20formulas%20and%20spilled%20array%20behavior%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdynamic-array-formulas-vs-legacy-cse-array-formulas-ca421f1b-fbb2-4c99-9924-df571bd4f1b4%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDynamic%20array%20formulas%20vs.%20legacy%20CSE%20array%20formulas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDynamic%20array%20formulas%20in%20non-dynamic%20aware%20Excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20opinion%20you%20should%20get%20the%20full%20points%20for%20the%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1767895%22%20slang%3D%22en-US%22%3ERe%3A%20Different%20Results%20between%20Professor's%20Excel%20and%20My%20Excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20lot%20of%20professors%20are%20still%20on%20Excel%202016%20since%20they%20have%20a%20lot%20of%20training%20materials%20built%20for%20this%20version.%20That's%20a%20headache%20to%20generate%20another%20set%20for%20DA%20Excel%20and%20keep%20these%20sets%20in%20parallel.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

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. 

 

excelhelp.JPG

2 Replies
Highlighted

@AuchnotOuch 

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.

 

Highlighted

@Detlef Lewin 

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.