Forum Discussion
Simple math formula returning multiple values (It should not)
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?
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.
Read this article to learn more about spilled array.
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.
michael_Korn1979 Perhaps you want to use:
=(B2*C2)+SUM(D3:J3)
- 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
