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.

Search JabSto ::

Custom Search