we will discuss four Microsoft Excel functions:
- IF THEN
At the end we will then combine the four functions in one example to demonstrate the power of these functions.
The absolute function returns the absolute value of a number. The absolute value of a number is the number without its sign.
The syntax for using the absolute functions is:
Where “number” is the real number of which you want the absolute value.
=ABS(3) equals 3
=ABS(-3) equals 3
If A1 contains -20, then:
=ABS(A1) equals 20
With text functions, you can manipulate text strings in formulas. The text function will convert a value to text in a specific number or date format.
The syntax for using the “TEXT” function is:
“Value” is a numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
“Format_text” is a number format in text form from in the Category box on the Number tab in the Format Cells dialog box. This will become clearer when you see an example. “Format_text” cannot contain an asterisk (*) and cannot be the General number format.
Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number.
=TEXT(2.715, “$0.00”) equals “$2.72”
=TEXT(“4/15/91”, “mmmm dd, yyyy”) equals “April 15, 1991”
There are numerous other text-type functions and we will discuss these in future newsletters.
This is an extremely powerful function and we will be using this throughout future newsletters.
The “IF THEN ” functions returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.
The syntax for the “IF THEN” statement is:
“Logical_test” is any value or expression that can be evaluated to TRUE or FALSE.
“Value_if_true” is the value that is returned if “logical_test” is TRUE. If “logical_test” is TRUE and “value_if_true” is omitted, TRUE is returned. “Value_if_true” can be another formula including another IF THEN function.
“Value_if_false” is the value that is returned if “logical_test” is FALSE. If “logical_test” is FALSE and “value_if_false” is omitted, FALSE is returned. “Value_if_false” can be another formula including another IF THEN function.
Up to seven IF THEN functions can be nested as “value_if_true” and “value_if_false” arguments to construct more elaborate tests.
If the value in cell A1 is 200, then “logical_test” is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, “logical_test” is FALSE, and empty text (“”) is returned that blanks the cell that contains the IF THEN function.
Suppose an expense worksheet contains in B2:B4 the following data for “Actual Expenses” for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for “Predicted Expenses” for the same periods: 900, 900, 925.
You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:
=IF(B2>C2,”Over Budget”,”OK”) equals “Over Budget”
=IF(B3>C3,”Over Budget”,”OK”) equals “OK”
The concatenate function joins several text strings into one text string.
The syntax for using this function is:
“Text 1″&” Test2″
Text1, text2, … are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.
“Total “&”Value” equals “Total Value”.
BRINGING IT ALL TOGETHER
Below is a link to a sample income statement for ABC Widget Company. To better understand this example you should download a copy of this spreadsheet by clicking here: Oct 1999 Sample
We have used the four functions above to generate dynamic notes to the income statement that will change with values. For example try changing the sales from $100,000 to $250,000. Notice how the notes about “Sales”, “Net operating revenue” and “Net income” all changed dynamically.
The formula to generate the note about the change in sales looks like this:
=”Sales for “&$E$6&IF(G10>0,” increased “,” decreased “)&TEXT(ABS(G10), “$0,000″)&” or “&TEXT(ABS(H10),”0%”)&” compared to “&$D$6Sponsored Links
$E$6 is the cell containing the name of the current month
G10 is the cell containing the dollar change in sales
H10 is the cell containing the percent change in sales
$D$6 is the cell containing the name of the previous month
The formulas for the other notes are similar.
This of course is a simply example, if you are able to import the statistical/operating data that drives you income statement you can generate very sophisticated notes to the income statement automatically.