Microsoft Office Tutorials and References

In Depth Information

**More of the Same**

76+82+58+91-58

Or 307-58, which equals 249. And why then do we need this pair of global parentheses around SUM

and MIN? Because of the
order of operations
, which assigns priority to expressions surrounded by those

parentheses, allowing us to treat the activity going in between them as one unit.

And once we derive it, that 249 is ultimately to be divided by 3—that is, the number of exams minus

1. Now take a look at our divisor:

(COUNT(B3:E3)-1)

And guess what—this expression is
also
surrounded by parentheses, and for exactly the same

reason—the order of operations. Remove those outside parentheses and our divisior would read,

formulaically:

COUNT(B3:E3)-1

and numerically:

249/4, then minus 1.

The result: 61.25.

But bring back those outer parentheses and you get:

249/(4-1)

or 83, the number we want.

As a matter of fact , if we peeled off the global parentheses on both sides of the divisor, our formula

would stand as:

=SUM(B3:D3)-MIN(B3:D3)/COUNT(B3:D3)-1

And that would yield us 291.5, not
even
close
to the number we want. Try it and you’ll see.

Thus writing formulas involves thinking your objectives through, fooling around with practice

formulas, making mistakes, and learning from them—and lining up those parentheses when you need

them (and Excel will be sure to notify you with an error message when the count of your open

parentheses doesn’t equal that of your closed ones, something like “Microsoft Excel found an error in

the formula you entered,” and will offer you a corrected suggestion. Click No to the suggestion and you’ll

be sent another message, observing that your expression as it stands is missing a parenthesis).

A final note on the above exercise. Even though our formula made important use of the SUM

function, we’d probably be advised not to write it and not to click the AutoSum button in order to post it

to its cell. And that’s because SUM deoesn’t stand alone in its cell this time; we needed to continue to

type
additional characters (incuding that first global parenthesis before the word SUM, which you won’t

get by clicking AutoSum) in order to combine SUM with the additional formula elements. Just

remember, though, that you can always type any function if you need or want to; and in this case, you

could type:

=(SUM(

and at that point
drag
the range B3:E3, continue to type:

)-MIN(

and then drag B3:E3 in order to identify that range for MIN, and continue to type. (And remember that

when you begin to type a function name, the Auto Complete menu will appear.)

Now thus far we’ve confined our discussion of functions to the ones that are presented to us on the

AutoSum drop-down menu. But as we stated earlier, there are hundreds more. Time and space will

restrict our treatment here to just a few of them, but once you get the general hang of these things,

learning additional ones will get that much easier.