Simple math formula returning multiple values (It should not)

[michael_Korn1979]

Hi.   

I have a simple work sheet setup it has 7 columns.   I am doing a formula =(B2*C2)+(D3:J3).   Excel keeps on wanting to return multiple results, using up 7 columns for the answer.  

In this case it is 3*51.67 + 75.  Each column does not have data, but I am using this same formula for multiple rows, in some other rows the other columns will have data.  First attempt I was leaving the other columns blank with no data, then I tried to just put 0 in those columns with no data, and it still returns the multiple results.   

Excel returns $230.01 in the first column, which is the correct answer.   Then it will put $155.01 (which is 3*51.67) in the other 6 columns next to it, with a box that says "Excel returned multiple results".   

Why is it doing this? 

[#2]

@michael_Korn1979 Perhaps you want to use:

=(B2*C2)+SUM(D3:J3)

[#3]

@michael_Korn1979 

Perhaps you meant =B2*C2+SUM(D3:J3) ?

Without a function such as SUM that returns one value, you'll get a multi-cell result in Excel 2021 and Excel 365.

[#4]

Ok that fixed the problem. Thank you. Now why was it doing that? I have used excel for years, and I have done it this way for years. Never had a problem until today

[#5]

@michael_Korn1979 

Read this article to learn more about spilled array.

https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b0...

[#6]

@michael_Korn1979 

It should.  If you specify

=(D3:J3)

you have referenced 7 cells and Excel will return 7 results.  That said, the behaviour is new in Excel 365 and 2021.  Older versions of Excel did something rather strange and picked a single value from the range or, sometimes, returned an error.