Functions and Formulas

Error Messages
Tired of those pesky error messages displaying in your worksheet after you use a Vlookup or sometimes just doing some division.  There are a couple of ways to do this but if you have Excel 2007, you want to become acquainted with the IFERROR function.

Converting CR to a Negative number
Ever download some information and then find out that you can't use it in calculations because negative numbers that have a CR at the end of them are treated as text by Excel? If so, there is a solution. Okay.. this is a bit complicated so you need to go through it step by step.

In Balance
Use this formula to test your balance sheet and see if it is in balance.

AND OR and NOT are logic functions that can be very useful. AND as well as OR allow you to test up to 30 conditions.

Shade Alternate Rows
=MOD and conditional formatting allow you to shade alternate rows. This is a must-know for any accountant - to increase readability in a long spreadsheet, shade alternate rows.

Some Time Functions

• Another way to use =MOD. (If you want to subtract the start and end time and they are not on the same day)
• Adding up Hours and Minutes
• Calculating Age or years of service