Use Statistical, Date and Time, Financial, and Logical Functions

Microsoft Excel spreadsheets have become somewhat of a standard for data storage,
at least for smaller data sets. This, along with the program often being packaged with new
computers, naturally encourages its use for statistical analyses. This is unfortunate, since
Excel is most decidedly not a statistical package.
Here’s an example of how the numerical inaccuracies in Excel can get you into trouble.
Consider the following data set:
Data Display
Row X Y
1 10000000001 1000000000.000
2 10000000002 1000000000.000
3 10000000003 1000000000.900
4 10000000004 1000000001.100
5 10000000005 1000000001.010
6 10000000006 1000000000.990
7 10000000007 1000000001.100
8 10000000008 1000000000.999
9 10000000009 1000000000.000
10 10000000010 1000000000.001

The Date and Time Components

In the table below I entered a date in cell B2, a time in C2, and a formula in D2 that adds the two together. Excel automatically formatted this cell using a m/d/yyyy hh:mm format. I changed the row 3 cell formatting to General so you can see the serial number values.

You can clearly see that Date is a integer value, Time is a decimal fraction, and the Date/Time format has both together in one number

Why Does the INT Function Extract a Date?

All Dates, Times, and Date-Times are know as serial numbers in Excel. You don’t normally see the underlying serial number. A Date-Time value like 9/14/2012 8:43 AM looks like that in the cell AND in the formula bar.

Only when you change the cell formatting to General do you see the Date-Time serial number. The integer portion is the date serial number and the fractional part is the time serial number.

This is why the INT function will work to extract a Date from a Date-Time value. The integer value is a date serial number. The trick is to change the cell formatting to a Date format so that Excel will show you the Date.

Logical Functions in Excel (IF, AND, OR, NOT)

Conditional and logical functions are used very often in Excel.  Most of the time we use them when we want to test if a certain criteria is met, and based on that result, we generate an output.  This gives our spreadsheet the power to make decisions.

Let’s look at the IF function first, probably the most common.  The IF function has the following format:

=IF(logical_test,value_if_true,value_if_false)

In this first example, we want to determine if students passed a course.  If they received at least 70% they passed, otherwise they failed.  We see that in C2 our formula is, IF(B6>=70%,return the text string PASS, otherwise return the text string FAIL).  We then copy and paste that formula down to the rest of the cells.  Remember that when we want to return a text string to a cell we have to put it in quotes, otherwise we get an error.  And we double check and it looks like our formula is correct since those students that got greater than or equal to 70% passed.  So that’s a simple example of an IF statement.

We can also nest IF statements within IF statements.  In this second example here, we have several people and their incomes listed.  We want to determine which income tax bracket they fall into based on the guidelines on the right.  So our formula is going to ask:  If the income is less than or equal to the upper limit of the tax bracket, return as a value that tax bracket, otherwise next check the upper limit for the next tax bracket and so on until it finds one that matches.  We see that the formula in C2 is:

=IF(B2<=8700, 10%, IF(B2<=35350, 15%, IF(B2<=85650, 25%, IF(B2<=178650, 28%, IF(B2<=388350, 33%, 35%)))))

After entering the formula we copy it all the way down for the rest of the names.  With nested IF statements, think of a big box that when you open it, has a smaller box in it, and that smaller box has an even smaller box within that, and when you open that one, you find an even smaller box.  And so on.  The nested IF statements keep checking for a condition until they find one that is TRUE, otherwise they will return a default value if nothing turns out to be TRUE.  And double-checking, it looks like our formula is correct since Excel gives us the correct tax brackets for all the people listed here.

Let’s move on to the AND logical function.  The AND function takes the form: