Cannot figure out why Excel objects to a simple forumula

try

=100-SUM(B1:B6)

Originally Posted by Coventry_Kid

#VALUE! is the result of the formula. But if I enter the formula as: +100-B1-B2-B3....etc., the correct result is returned.

If that is your intent, then write =100-SUM(B1:B4) .

(Aha! JT beat me to posting that solution. But the following might still be helpful -- or TMI.)

When you write =100-B1:B4, Excel does not know how to interpret -B1:B4.

In fact, be thankful that Excel returned a #VALUE error. If you entered =100-B1:B4 somewhere in row 1 through 4, Excel would have returned a numeric value, which you might not have realized was incorrect.

In the context =100-B1:B4, the form of B1:B4 is interpreted as "implicit interception" in older versions of Excel. That is, an array of values from which Excel selects a single value, depending on the row in which the formula is entered.

I'm not sure how newer versions of Excel interpret B1:B4 in that context. The "implicit" interception would be denoted by the operator "@", to wit: =100-@B1:B4. But even then, I suspect that Excel would not know what to do with @B1:B4.

PS.... Oh! I keep forgetting that I can try these things in Excel Online, a dumbed-down version of Office 365 Excel, I think. If we enter =100-B1:B4, Excel Online does a "spill", which behaves like a range of formulas of the form =100-B1 copied down. But =100-@B1:B4 still returns a #VALUE# error, again because Excel does not know what to do with @B1:B4.