Microsoft Office Tutorials and References

In Depth Information

**CHAPTER 1 Functions Overview**

Table 1.2

Keystroke Operators

Text

Explanation

Example

Adjoins text

&

“Scott” & “Blattner”

produce “ScottBlattner”

Reference

Explanation

Example

One reference between all cells

:

B3:B20

Separates arguments in a function

,

(B3,B20)

Operator Order

When creating complicated formulas or formulas that include more than one operator, there

is a specific order to use. The order is listed in Table 1.3. Meaning, if I were to create a

formula that contained
%
and the operator
+
, the
%
would come first.

Table 1.3

Excel’s Operator Order

Operator Order

Example

1

%
Percent

2

^
Exponentiation

3

*/
Multiplication before Division

4

+
– Addition and then Subtraction

5

&
Ampersand (adjoins text)

6

>
,
>=
,
<
,
<=
,
=
,
<>
Comparisons

Custom Functions

You can create custom functions in Excel, however, be sure Excel has not already addressed

a function that will work for your specific needs. Note the following custom function example.

If I was in the construction industry, and had a specific calculation that included a formula

with rates that were specific to my company and equipment, and a profit rate that was also

specific to my company, I would want to create the custom function
=TRUCKRATE(xxx)
. Now,

every time that I estimate an amount of material to be moved and it includes the use of a Cat

777 truck, it would calculate the amount of material based on the 777 truck base rate plus

overhead. See Chapter 2, “Managing Your Business with Functions,” to learn more about

creating custom functions.

The following quick reference will help you find a function that will work for your specific needs.

To learn more about the function, see its detailed description in the corresponding chapter.

As you will see, the lists below are grouped to reflect the structure and layout of this topic.