As a project manager, one of the tools I live by is the Excel spreadsheet. Budgets, reports, even project schedules begin in this Microsoft powerhouse before sometimes moving into more “official” project tools. Over the years, I have been shown or discovered for myself, a few things in Excel that have made life so much easier.
=IFERROR(INDEX(…),””)
The first is a formula called IFERROR(). It is less of a formula than it is a troubleshooting assistant. When building spreadsheets, I always come across DIV/0, #NAME, #N/A, #REF type issues. Introduced in Excel 2007, IFERROR() formula checks a formula (or expression) and returns the value of the formula if there is no error. Otherwise, it displays a custom formula.
You can use this in formulas such as VLOOKUP, INDEX+MATCH to search for missing values in your data. I typically wrap the formulas in IFERROR to provide the error validation.
On reference formulas like INDEX, OFFSET, frequently, we try to fetch the data that is not in the list of values. These return #REF errors. These are easily fixed by replacing the #REF with a blank cell.
Here are a few examples:
- =IFERROR(VLOOKUP(…),”Not found”) This provides the text “Not found” in every cell where a matching value is not located.
- =IFERROR(AVERAGE(…),”0″) +0 In my opinion, this is probably one of the most useful applications of the formula. When trying to suppress DIV/0 errors, this will essentially zero-out the formula. The “+0” at the end converts the value to a number.
Now, what does this mean to the spreadsheet developer? In some cases, it simply provides you with a transparent set of values that do not affect calculations or reports, but the true value comes in when you sort and filter on the custom text entered in the formula.
=NETWORKDAYS()
The next formula I use quite a bit is NETWORKDAYS(). This formula was introduced in Excel 2010 and was a bit of a lifesaver for many of us. The essential function is to take two dates and return the amount of work days in-between. So for example, if you run the formula for 11/01/2016 and 12/31/2016, you will get a value of 22. But wait, there’s more. Notice this additional variable you can enter:
This allows you to create a range of holidays which are then subtracted from the calculation.
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
If you use Excel and do any amount of reading about it, you will find this to be one of the most widely used formulas in the tool box. This is Microsoft’s description of the formula: “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…”.
That is just a complex way of saying, define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array), if it matches the “lookup_value” in the left column of the “table_array,” it returns the value in the specified column using the “index_num.” The “index_num” is the column. For example, A is 1; B is 2 and so forth. The “range_lookup” is a TRUE or FALSE value. If you put ‘TRUE,’ it will give you the closest match. If you put ‘FALSE,’ it will only give you an exact match. I tend only to use false unless I’m doing data scrubbing.
Finally, a few good formulas to know for day-to-day use:
- LOWER – Converts text to lowercase
- UPPER – Converts text to uppercase
- PROPER – Capitalizes the first letter of each word (This one can be a bit unpredictable.)
- CLEAN – Removes all nonprintable characters from text
- NOW – Returns the serial number of the current date and time
- TODAY – Returns the serial number of today’s date
- TRIM – Removes unnecessary spaces from the beginning, middle, and end of text
As with the previous formulas, these are formatted in the =formula() format.
Found this article interesting and useful? Want to learn more about Excel formulas? Contact KTL Solutions at [email url=”info@ktlsolutions.com” class=””]info@ktlsolutions.com[/email] or call us at 301.360.0001.
