Right Wing Nation

Formulas take the form:

=C12/D54

Note that other than the initial equals sign, Excel formulas are algebraic expressions. The order of operations is the same, and exceptions are denoted by using parentheses, just as in algebra, so:

C12/D54+B13

and

C12/(D54+B13)

are different expressions.

Neither formulas nor functions may contain spaces. Also, any text in a function must be enclosed in quotation marks.

Functions take the following form:

=FUNCTION(argument₁,argument₂, …)

Parentheses are used to set off the argument list, not change the order of precedence. Functions are calculated inside out, so if you have a complex function, Excel will calculate the innermost, and work its way to the outside. Note that even functions that do not take arguments must appear with an argument list inside parentheses, so the TODAY function is entered:

=TODAY()

The functions we’ll cover now are:

• SUM
• AVERAGE
• COUNTA
• COUNT
• SUMIF
• AVERAGEIF
• COUNTIF
• SUMPRODUCT
• TODAY
• DATEDIF
• ROUND
• INT
• RAND

SUM

The SUM function sums a range (row or column), and takes only the range as a single argument. This function is so frequently used that it has its own button.

AVERAGE

Does exactly what you think, and like SUM, takes only one argument. Note to instructors: You cannot use this function to calculate class means because it includes zeroes. If you use Office 2007, see AVERAGEIF below; if you use a previous version, see COUNTIF below.

COUNTA

Takes one argument, a range, and returns the number of non-blank (text or numeric) cells. Not to be confused with:

COUNT

which takes a range as its argument and returns the number of cells containing numeric data.

The next three are technically conditional functions, but belong with the related functions here, and are extremely simple.

SUMIF

This function sums the contents of one range that match a condition specified in another (or the same) range. It takes three arguments: The range that contains the condition, the condition (note that this must be enclosed in quotation marks, as must any text), and the range you want to sum, or:

=SUMIF(condition_range,”condition”,sum_range)

Say we want to know the sum of all orders over $1000. Here’s how we would do it:

And =SUMIF(A3:A10,”3″,C3:C10) would calculate the sum of all orders for sales rep 3.

AVERAGEIF

This averages all of the cells in the range that match the specified condition. If you’re calculating a class mean:

=AVERAGEIF(range,”>0″)

If we had used the AVERAGE function, it would have artifically lowered the mean. The above function counts the number of non-blank cells that are greater than zero.

Office 2007 was the first version of Excel to include this function.

COUNTIF

This function counts all of the non-blank cells in a range (first argument) that match the specified condition (second argument). Before Office 2007, here’s how we had to calculate class means:

=SUM(range)/COUNTIF(range,”>0″)

=COUNTIF(C3:C10,”<=1000") would return the number of sales less than or equal to $1000.

By the way, did you know that the probability of a student scoring zero on a 100-point exam with four distractors on each question (assuming that the student gave a response for each answer) is 0.000000000032?

Sorry, I got distracted.

SUMPRODUCT

This function calculates the sum of the products of two arrays. Confused?

=SUMPRODUCT(A2:A6,B2:B6)

is the same as

=A2*B2+A3*B3+A4*B4+A5*B5+A6*B6

The two arrays must be contain the same number of numeric values.

TODAY

This incredibly useful function (=TODAY()) returns the current date.

DATEDIF

This calculates the number of days, months, or years between two dates. The date arguments appear in chronological order:

=DATEDIF(begin_date,end_date,”d”)
=DATEDIF(begin_date,end_date,”m”)
=DATEDIF(begin_date,end_date,”y”)

A related function, NETWORKDAYS, calculates the number of week days between two dates.

ROUND

The round function takes a numeric cell or the results of a calculation and rounds it to a specified number of places:

=ROUND(cell,1)
=ROUND(SUM(C2:C65),1))

Sometimes, you need an integer, but you don’t want rounding, so:

INT

This function “cuts off” anything to the right of the decimal point, and takes one argument, like ROUND. Here’s an example.

What if we want the number of weeks between two dates? DATEDIF does not take “w” as an argument, so we have to calculate the number of days, and divide by 7. However, that will almost always give us a decimal, and because of the way we count time (you’re never 33.33 years old, and don’t turn 34 until your 34th birthday), we can’t use the ROUND function. Instead, we use INT:

=INT(DATEDIF(begin_date,end_date,”d”)/7)

Finally, we have the RAND function, which takes no arguments, and returns a random number between 0 and 1. Okay, well not really. No computer can generate a random number. The computer uses algorithms to simulate a random number. It looks like this:

=RAND()

Trackback URI | Comments RSS