CSE Formulas Not Evaluating

%3CLINGO-SUB%20id%3D%22lingo-sub-2005035%22%20slang%3D%22en-US%22%3ECSE%20Formulas%20Not%20Evaluating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005035%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20trying%20to%20learn%20to%20use%20CSE%20%2F%20control%2Bshift%2Benter%20formulas%20in%20workbooks%20to%20improve%20my%20Excel%20usage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20and%20well%20failing%20to%20get%20a%20simple%20CSE%20formula%20to%20evaluate.%20Another%20simple%20example%20such%20as%20%3D%7BSUM(ColumnA*ColumnB)%7D%20to%20calculate%20sum%20products%20of%20two%20columns%20works%20as%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20trying%20to%20use%20this%20formula%20%3D%7BA2%3AA4*B1%3AD1%7D%2C%20where%20A2%3AA4%20and%20B1%3AD1%20are%20just%20three%20adjacent%20cells%20going%201%2C%202%20and%203.%20For%20some%20reason%20when%20I%20try%20to%20evaluate%20this%20formula%20use%20CSE%20it%20just%20doesn't%20fill%20out%20in%20the%20workbook.%20Instead%20it%20just%20calculates%20the%20first%20element%20in%20the%20cell%2C%20where%20the%20formula%20was%20typed%20in.%20It%20shows%20the%20formula%20enclosed%20%7B%7D%20in%20curly%20brackets%20but%20it%20hasn't%20spilled%20over%20the%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20of%20course%20a%20simple%20example%20I'm%20not%20using%20for%20a%20real%20life%20job%2C%20its%20just%20irritating%20as%20I%20can't%20see%20why%20it%20isn't%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20looking%20at%20my%20settings%20to%20see%20anything%20obvious%20I've%20missed.%20I've%20also%20tried%20looking%20around%20looking%20around%20to%20no%20avail.%20I'm%20using%20MS%20Office%202016.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2005035%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-2005098%22%20slang%3D%22en-US%22%3ERe%3A%20CSE%20Formulas%20Not%20Evaluating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005098%22%20slang%3D%22en-US%22%3ESelect%20cells%20B2%3AD4%20first%2C%20then%20click%20in%20the%20formula%20bar%20and%20re-confirm%20the%20formula%20w%2F%20CSE.%20To%20see%20the%20entire%20array%2C%20you%20have%20to%20first%20select%20a%20range%20that%20is%20the%20same%20size%20as%20the%20array%20that%20will%20be%20returned.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2005275%22%20slang%3D%22en-US%22%3ERe%3A%20CSE%20Formulas%20Not%20Evaluating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005275%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F907697%22%20target%3D%22_blank%22%3E%40tham11265%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20because%20you%20just%20select%20one%20cell%20when%20you%20write%20the%20formula%2C%3CBR%20%2F%3EYou%20should%20select%20the%20same%20number%20of%20cells%20that%20you%20will%20do%20the%20operation%20on%2C%20in%20your%20case%20is%203%3C%2FP%3E%3CP%3EIf%20you%20do%20not%20select%20the%20cells%2C%20you%20will%20get%20the%20result%20for%20the%20first%20cell%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22array.png%22%20style%3D%22width%3A%20383px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241984iFE4DFB1135B505CF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22array.png%22%20alt%3D%22array.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi,

 

I'm currently trying to learn to use CSE / control+shift+enter formulas in workbooks to improve my Excel usage.

 

I'm trying and well failing to get a simple CSE formula to evaluate. Another simple example such as ={SUM(ColumnA*ColumnB)} to calculate sum products of two columns works as expected.

 

I'm currently trying to use this formula ={A2:A4*B1:D1}, where A2:A4 and B1:D1 are just three adjacent cells going 1, 2 and 3. For some reason when I try to evaluate this formula use CSE it just doesn't fill out in the workbook. Instead it just calculates the first element in the cell, where the formula was typed in. It shows the formula enclosed {} in curly brackets but it hasn't spilled over the range.

 

This is of course a simple example I'm not using for a real life job, its just irritating as I can't see why it isn't working.

 

I've tried looking at my settings to see anything obvious I've missed. I've also tried looking around looking around to no avail. I'm using MS Office 2016.

 

Any help appreciated, thank you.

3 Replies
Select cells B2:D4 first, then click in the formula bar and re-confirm the formula w/ CSE. To see the entire array, you have to first select a range that is the same size as the array that will be returned.

Hi @tham11265 

 

This is because you just select one cell when you write the formula,
You should select the same number of cells that you will do the operation on, in your case is 3

If you do not select the cells, you will get the result for the first cell
array.png

@tham11265 

As a comments, it's better to avoid CSE