Microsoft Office Tutorials and References

In Depth Information

**Looking for a Sign**

The INT and TRUNC functions work exactly the same way for positive

numbers. The only difference is when negative numbers are being changed. Then

INT’s rounding produces a different result than TRUNC’s truncation.

Looking for a Sign

Excel’s SIGN function tells you whether a number is positive or negative. The

SIGN function does not alter the number in any way but instead is used to

find out information about the number.

SIGN does actually return a number, but it isn’t a variation of the number

being tested in the function. SIGN returns only three different numbers:

✓
1 if the number being tested is positive

✓
–1 if the number being tested is negative

✓
0 if the number being tested is 0

Consider these examples:

✓
=SIGN(5) returns 1.

✓
=SIGN(-5) returns –1.

✓
=SIGN(0) returns 0.

Using SIGN in combination with other functions presents sophisticated ways

of working with your information. As an example, you may be tallying up a

day’s receipts from your store. You want to know the total value of sold

merchandise and the total value of returned merchandise. Sales are recorded as

positive amounts, and returns are recorded as negative amounts.

Figure 7-8 shows a worksheet with these facts. Column A shows individual

transaction amounts. Most are sales and are positive. A few returns occurred

during the day, entered as negative amounts.

Just summing the whole transaction list would calculate the net revenue of

the day, but often a business needs better information. Instead, two sums are

calculated: the sum of sales and the sum of returns.

For each value in Column A, there is a value in Column B. The Column B

values are the result of using the SIGN function. For example, cell B3 has this

formula: =SIGN(A3).