Use Excel's IF statement to track, save on IT support contract costs

In my previous articles, we looked at what Microsoft Excel offers to IT administrators. Not only can you use pivot tables to review support contracts, but you can also sort Excel spreadsheet data to verify warranties and license terms.

Table 1

To make this spreadsheet work, we manipulate the Year and Month functions into an IF statement (see Table 1). The raw format of each function is:

• Year(serial_number). This returns the year when the serial number of that year is provided. Not to worry -- when a date is provided in a cell, the Year function reads its serial number. You can then manipulate the year, as shown in the example. For example, if cell C2 had the date 04-27-2011, then Year(C2) would return the year portion of the date in C2, or 2011.
• Month(serial_number). Same as Year function but returns the month.
• IF (logical_test,value_if_true,value_if_false) the "logical_test" is a test such as C2>0. The "value_if_true" is what IF will return if the test is true, and "value_if_false" is what IF will return if the test is false.
• Example: =IF (Year(C2)>2014,"yes","no")
  • If the year in cell C2 is >2014, return Yes, otherwise return No. The result here would be No since Year (C2) is 2011 (see previous example).

The IF statement can be nested. For instance, if we want to test whether the year in C2 is greater than 2014, we could nest a second IF statement in the "value_if_false" argument:

=IF Year(C2)>2014,"Greater",IF(Year(C2)=2014,"Equal","Less".

This reads: IF Year(C2) is greater than 2014, then return "Greater" (the value_if_true test). If this is false -- C2 is not greater than 2014 -- then go to the next IF statement in the value_if_false test.

In this statement, we test if the Year(C2) is equal to 2014; if it is (the value_if_true test) then we return "Equal." If it is not equal, and since we already tested for Greater, then the Year(C2) must be less than 2014, and we return "Less."

For our warranty table in cell E2, we need to know how many months this device will be out of warranty in 2014. If we know the number of months it is in warranty, we can subtract that from 12.

Multiplying the out-of-warranty months (E2) by the monthly non-warranty support cost (D2) returns the total cost for that device in 2014.

We also want to find the cost for this device for the years 2015 and 2016, since our support contract is three years. The formula for E2 would be:

=IF(YEAR($C2)>2014,0,IF(YEAR($C2)=2014,12-MONTH($C2),12))

If the year in cell C2 is greater than 2014, then return zero. If the year is, say, 2015, then the device is in warranty all of 2014, and there will be zero months out of warranty and no cost in 2014. This is the "value_if_true" test.

The "value_if_false" test is a nested IF statement. Here we test if Year (C2) is = 2014. If it is, then the warranty expires in 2014, and we have no need to know how many months are not in warranty.

Using the MONTH function, we subtract MONTH($C2) from 12. So, if the warranty expires in April 2014, then Month($C2) will be four, and the result in Column E would be eight.

The "value_if_false" argument for the nested IF statement will be 12 because if it fails the greater-than test and the equal-to test, then the Year($C2) must be less than 2014. This means if the year is 2013, it returns a 12, meaning the warranty expired before 2014, so we must pay the monthly out of warranty fee for all 12 months.

Note: Watch the parentheses. Each Cell reference must be in them, and each IF statement must also be in parentheses. An easy way to make sure you have the proper number of left and right parentheses is to count them. If you count five left parens and six right parens, you are missing a left parenthesis.

Note 2: The cell with the date, C2, is prefixed with a dollar sign. This allows the formula to be copied to other cells without dynamically adjusting the cell to D2.

For 2015 (H2) and 2016 (H3), we simply have to copy the formula we just created for E2 and change the years from 2014 to 2015 and 2016, respectively.

Let's summarize the cells for each year. Columns D, G and J will have a static value for the monthly out-of-warranty support cost. You may use an Excel function such as VLookup or HLookup to input this from another worksheet or file. You can also just cut and paste it if the rows are the same as the input sheet.

Columns E, H and K will have the appropriate IF statement shown previously.

Column F will simply multiply the value in Column E with that in Column D=D2*E2 and similarly for columns I and L. You can also summarize each column (F, I and L) to get annual support costs for these devices.

Excel is a powerful tool for a desktop administrator who wants to take his job description to the next level. Tracking support costs can help with budgetary requirements from IT management. It can also help verify that suppliers' monthly charges are correct and turn any admin into a cost-savings hero.