Category Archives: Functions

Adding VLookup as a Control for Your Month-End Workpapers

VLOOKUP As a CONTROL

When closing for the month, many use an Excel workbook to reconcile balances to the general ledger.  As the general ledger changes, do you have a process in place to easily determine that balances previously reconciled still tie to the general ledger?  If not, using Vlookup on each reconciliation worksheet provides a quick and easy way to accomplish this control.

Usually, the month-end balance from a reconciliation worksheet is at the bottom of the worksheet.  Below this balance, you can enter the associated general ledger account number to be used in a Vlookup formula.  After the account number and Vlookup formula have been entered in each workbook, all you need to do is cut and paste the most recent general ledger balances into a separate worksheet.  The Vlookup will display the most recent balance and you can use conditional formatting to highlight whether the two balances are within a tolerable error limit.

The example below assumes that the AR Trial Balance has been created and the total needs to be compared to the general ledger.  There is a worksheet labeled “Accounts Receivable” and a separate worksheet labeled “General ledger”.

The general ledger account for accounts receivable has been entered at the bottom of the Accounts Receivable worksheet and a Vlookup formula is used to compare this total to the amount in the General Ledger worksheet.

vlookup as control 1

 

 

 

 

 

 

 

The Vlookup function is searching for a worksheet titled “General ledger” in the same workbook.  The key to making this work is that you never delete this worksheet.  When the general ledger changes, simply paste the new general ledger data over the old data in the same worksheet.

For Vlookup to work, the lookup value, in this case the general ledger account number, must be in the leftmost column of the General Ledger worksheet (In this example, column A).  The general ledger data is in columns A and B and the account balance is in column 2 (which is the B column).  The FALSE range lookup value is used to indicate an exact match.  The example general ledger is shown below.

 

 

 

 

 

 

 

 

Why use the entire columns of A and B?  That way, if general ledger accounts are added, we’re not constrained by a limited range.  This is important as we want to paste over this data again and again without using new Vlookup formulas each time.

To calculate any differences, subtract the general ledger balance from the reconciliation balance.  Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number).

 

 

 

 

 

 

 

In this case, the difference is 0.  However, we want a way to highlight a difference if it exceeds a tolerable error limit.  Assume that we want to highlight any change greater than $100 and, if the difference is greater than $100, highlight the cell in yellow.

Select cell B13 and then select the Conditional Formatting button on the Excel Home ribbon.  From the Conditional Formatting options, select “Highlight Cell Rules”, then “Greater Than”.

conditional formatting vlookup

 

 

 

 

 

 

 

 

 

 

 

Once “Greater Than” is selected, you have the opportunity to input both an amount and highlight colors.  In this example, our amount is greater than 100 and we’ll use yellow fill with a yellow text to highlight the difference.  Note that we used an absolute value since the difference could be either greater than 100 or less than 100, depending on whether the general ledger amount was more or less than then the reconciliation amount.  Having only one value makes the Conditional Formatting comparison much easier.

Vlookup and conditional formatting

 

 

 

 

 

 

 

 

Press OK.

Since the difference is not currently greater than 100, there should be no yellow highlight.  If the general ledger balance is changed to 31,500 (a difference that is greater than 100), the difference is now highlighted.

ABS

With this Vlookup control in place for each month-end reconciliation worksheet, you can copy and paste the new general ledger data into the General Ledger worksheet and the new balances will update.  Any differences that exceed a tolerable error amount will be highlighted and you can simply scan each worksheet to determine if a difference exists.

This process is much easier to implement if all month-end account reconciliations are in one workbook.  That way, the general ledger data only needs to be pasted once in the General Ledger worksheet each time the general ledger is updated.

CPASelfStudy has several in-depth courses on Vlookup and Conditional Formatting.  If you want more information or other productivity ideas, please check out our Excel course offerings.

 

 

 

Using TextJoin

text join

 

 

 

 

 

TextJoin()

TEXTJOIN is a text function that was introduced in Excel 2016 to join text in cells together.
Initially, you may think that the function is not that worthwhile. After all, we already have the ampersand (& ) as well as the CONCATENATE function to join text together.
However, TEXTJOIN really shines when you want to join multiple cells together as you only have to specify the delimiter once.
The syntax is =TEXTJOIN(delimiter, ignore empty, text…)

textjoin function

 

 

 

 

 

 

 

 

 

Let me give you a simple example using a person’s first, middle and last name and their professional designation.
textjoin function

 

 

If we used CONCATENATE, the equation would be : =CONCATENATE(A2,” “,B2,” “,C2,” “,D2).
If we used  TEXTJOIN it is,  =TEXTJOIN(” “,,A2:D2)

textjoin text function

 

 

 

 

 

 

 

 

 

and this result would display:

Lisa Marie Brown CPA

Clearly, TEXTJOIN is a bit simpler and a bit more elegant if you have a lot of cells that you want to join together and you want to use the same delimiter throughout.
You can use different delimiters if you wish and it is still simpler than CONCATENATE as well. In this example, I just wanted spaces after each of the names but I wanted a comma before the professional designation so I used =TEXTJOIN(” “,,A2,B2,C2,”,”,D2) and Excel displayed  the following result.

Lisa Marie Brown , CPA

So, add TEXTJOIN to your arsenal of text functions if you have Excel 2016.

 

 

SUBTOTAL() and AGGREGATE() -TimeSavers

subtotal and aggregate - time saversMany people are only aware of the Subtotal icon and have never looked at the Subtotal function itself. After all, the icon, lets you add, average and count a list so who needs more? Well if you are one of the people who subtotal data a lot then you may want to look at the Subtotal function or the AGGREGATE() function.

Let’s start with SUBTOTAL().
There are 3 advantages of using the SUBTOTAL function rather than the icon.

  • First, the subtotal function allows you to display the subtotal wherever you want it to display on the worksheet rather than in the list itself.
  • Second, you can specify if you want Excel to subtotal all of the data in the list, even the hidden values, or to only subtotal the visible data in the list. That is powerful.

The syntax for SUBTOTAL is =SUBTOTAL(function_num, ref1,[ref2]…). The function_num indicates the mathematical operation you want Excel to do. For example, =Subtotal(1,B2:B50) tells Excel to Average the data in cells B2 through B50. Apparently the programmers sequenced the function_nums alphabetically rather than on frequency as you would normally expect that 1 would be to add – or at least I did. Anyway, I digress. 1 means to average, 2 to count and 9 means to sum.  By using the single digit function_number, Excel automatically includes all hidden rows and columns in the list.  If you use the corresponding 3 digit function_number, Excel subtotals only the visible list in the data. So, the function_num 1 includes the hidden rows, while 101 excludes them.  Below is a list of the most common function_num:

subtotal()

  • The third advantage of the Subtotal function is that it excludes other subtotals. So, for example, if you had a monthly report that has a lot of report sections such as Sales, Cost of Goods Sold, Operating Expenses, Non-Operating Gains(Losses) , typically you add up each grouping and then create a grand total. If you used =SUBTOTAL() on each of these groupings than all you need to do is use =SUBTOTAL() at the end of the report to get a grand total because SUBTOTAL() does not include subtotals when it totals!  How cool is that?  This is also a time saver if you need to create a new section for something new within the report as the grand total will automatically update if =SUBTOTAL() was used.

AGGREGATE()

If you are excited about =SUBTOTAL() then check out =AGGREGATE() if you have Excel 2010 or higher. AGGREGATE() does everything that SUBTOTAL() does and more. While =SUBTOTAL() offers 11 functions, AGGREGATE() offers 19 functions. Some of the more useful new functions include SMALL() and LARGE(). In addition to offering you the option of ignoring hidden rows of data, it also allows you the option of ignoring  error values and/or subtotals.  This is key – how often have you tried to sum a column of data that has a #Div/0! Or a #N/A? Well, if you use AGGREGATE() you will actually get an answer instead of an error message in the total line.

The syntax of AGGREGATE() is similar to SUBTOTAL() but gives you more control over what you are totaling so it is more powerful.. It is AGGREGATE(function_num, options, ref1,[ref2],..]. A function_num of 1 still means Average while a 9 means Sum. The difference here is the options section. The option number tells Excel which values to ignore in the data list you are testing.

AGGREGATE()

 

 

 

 

 

 

SUMMARY

So, why didn’t I lead with AGGREGATE()? I started with =SUBTOTAL() because a lot of people are familiar with the Subtotal Icon and it is a bit simpler. However, if you are a manager of lots of data, I suggest you jump over SUBTOTAL() and use AGGREGATE().
Below is a simple example comparing SUM(), SUBTOTAL(), AGGREGATE().

Subtotal() aggregate()

 

 

 

Using TRUNC()

This is an excerpt from our Excel CPE course – Must Know Excel tips Tricks and Tools for CPAs.

TRUNC()
No Decimals
TRUN - no pennies

If you don’t want the pennies in your calculation you can use =TRUNC(), short for TRUNCATE, or =INT(), short for INTEGER,  to turn your data into integers.

 
The two functions work differently but return the same value when working with positive numbers. You will get different answers from these two functions with negative numbers. TRUNC() removes the fractional part of the number whereas INT() rounds down to the nearest integer based on the value of the fractional part.

In the screen shot below, -6.45 returns a different value using INT as it -7 is the lower value.

TRUNC

 

Understanding Excel Time Value of Money Functions

Understanding Excel Time Value of Money Functions

Below is an excerpt from our  Excel Time Value of Money Functions for CPAs course.

There are certain terms and conventions in Excel that are extremely important to understand prior to working with Excel time value of money functions.  The most important to understand are the terms used in Excel.

Most Excel time value of money functions contain four or five basic inputs.  They are:

  1. Pv – present value. Used for both single sums and annuities.
  2. Fv – future value. Used for both single sums and annuities.
  3. Nper – number of periods. Used for both single sums and annuities.
  4. Rate – interest rate for period. Used for both single sums and annuities.
  5. Pmt – periodic payment. Used only for annuities.

Functions may be entered directly or a function wizard may be used for input.  If the functions are entered directly, the required inputs and structure are below.

  1. =Pv(rate, nper, pv,fv, type)
  2. =Fv(rate, nper, pmt, pv, type)
  3. =Nper(rate, pmt, pv, fv, type)
  4. =Rate(nper, pmt, pv, fv, type, guess)
  5. =Pmt(rate,nper,pv,fv,type)

Keep in mExcel time value of money ;ind that, at a minimum, only three of the basic inputs are required to arrive at the correct answer.  Type (not one of the basic inputs) refers to when annuity payments are made (either at the beginning or end of a period) and, if omitted, the default is at the end of a period.  Guess (also not one of the basic inputs) is literally a guess of the correct answer and helps Excel narrow its calculations. It is not required, but may be necessary in the IRR calculation to arrive at an answer.

Most find it easier to use the function wizard as it is not necessary to remember the function inputs and you can see the function result prior to clicking “OK”.  For the Pv function, the function wizard is displayed below.

Excel uses a sign convention that indicates whether an amount is a cash inflow or cash outflow.  It gets a little confusing.  Basically, if you’re investing, that amount would be reflected as a negative amount – a cash outflow to the investment.  If you’re receiving a loan, that would be a positive amount as it is a cash inflow to you.  A cash payment for a loan (Pmt) would have a negative sign while cash received from an investment would have a positive sign.  Some people are confused when they compute a payment or a present or future value and it results in a negative amount.  However, there are certain calculations where the sign convention is very important.  This will also be covered in greater detail in the specific examples contained in the course.

Of the five (or six) function arguments that are available, make sure that if they’re not needed, they are set to blank or zero.  Anything other than blank or zero will enter into the calculation and make the result incorrect.  As an example, if you want to know the present value of a single sum and your inputs are Rate, Nper, and Fv, make sure that Pmt has a value of blank or zero as this argument is not needed.  While Type should have a value of zero as well, it is ignored in a single sum calculation.  However, in an annuity example, you would need to be more aware of the Type argument as it would have an impact on the result.

Excel does not have an option for compounding frequency.  In Excel, Nper must be the total number of periods and Rate must be adjusted so that it conforms to a per period basis.  As an example, if you’re dealing with a 5 year loan at an annual interest rate of 12% where payments are monthly, there are a total of 60 monthly payments in 5 years.  Nper in this case is 60.  Since the payments are monthly, Rate must also be adjusted from an annual rate to a monthly rate by dividing the annual rate by 12.

The Rate argument must be entered as a decimal.  If you want to enter 12%, it must be reflected as .12.  Having 12 as an input in this example would provide an incorrect result.

The “Type” argument is only used in annuity calculations and is used to establish whether the payment is made at the beginning of the period or at the end of a period.  The default in Excel is at the end of a period.  However, there are instances where the cash flow occurs at the beginning of the period and, in these instances, you must change the Type argument to 1.    In an annuity calculation, the resulting answer will be different depending on the Type argument selected.

While not required, it is suggested that cell references be used as inputs for these functions.  By doing this, it is easier to review and should result in a lower likelihood of input errors.  It also makes it easier to change inputs.

RRI Function

Below is an excerpt from our Excel Time Value of Money course written by Joseph Helstrom,CPA.

RRI

RRI is a new function that was introduced in Excel 2013. It returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.
As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  In these examples, we will type the formula directly into the cell and not use the function wizard.
Start in the cell with =RRI( and then type the inputs as shown below.
RRI function

 

 

 

The answer:

RRI - Time value of money function

About 5.2%.

Remember, compounding periods are important.  What if the investment was for 8 years compounded quarterly?  You would need to adjust NPER for the quarterly compounding so it would be 8 years x 4 compounding periods per year = 32.  You will also need to adjust the result if the desired answer is an annual rate.  You can type a new formula by starting with =RRI( and then typing the new inputs or selecting the cell and pressing F2 to edit the existing formula.
RRI function

 

 

The answer:

RRI -Time Value of Money function

 

 

This is the answer for a quarterly rate.  When multiplied by 4 to translate it into an annual rate, the answer is .012751 x 4 = .051004 or about 5.1%.
The quarterly compounding slightly reduced the rate necessary to achieve the same result.

DATEVALUE Function

Excel has a number of date functions.  Unfortunately, they don’t all work if Excel treats the date as text.  When working with downloaded data, sometimes the date must be converted from text to a format recognized by Excel.

One method is to use the DATEVALUE function.  This function converts a date in text format to a number that represents the date in Excel date-time code.
Open the exercise workbook and select the Datevalue worksheet.

In cell A1, is the text ‘12/31/11.  In cell B1, type =DATEVALUE(A1)
datevalue 1

The value in cell B1 is the Excel date-time code.  However, it requires some formatting to make sense.
Format cell B1 as a date either by using the Home menu and Number selection or the Home menu Format Cells selection.

datevalue2Format B1 in the date format of xx/xx/xxxx.datevalue3

You can tell that the date in cell A1 is in text format due to its left justification in the cell while the date in B1 is a date by its right justification in the cell.

This is an excerpt from our Must Know Functions for CPAs.

LEN Function

LEN Function – Counting Characters in a Cell

 

LEN() is one of those functions that you wonder about. LEN is a text function and depending upon what you are doing it is so useful. It counts the number of characters in a cell.  I use it all the time if I want to drop a minus sign at the end of a number of  if I imported values that have turned into text because the numbers have a CR after them.

In this example, I had a column of stores and they all began with Contoso. Since it was a bit redundant I wanted to get rid of the word Contoso at the beginning of each store name.

LEN function

Now, using =RIGHT(A2,18) works for the first and second store as they are 18 characters in length when Contoso is excluded but the Kennewick Store only needs 16 characters instead of 18 so the formula doesn’t work for that store or some of the others on the list that are of different lengths.
An easy way around this is to combine RIGHT with LEN.

=LEN(A2) = 26 counts  26 characters in cell A2.
Every store name has the word Contoso  in it and Contoso has 7 characters and that is what we want to eliminate.

So, =RIGHT(A2,LEN(A2)-7) is the answer.
When you break it down the formula ends up being =RIGHT(26-7) for the first store. 26 characters less the first 7 characters. Same for the second store. Now the store name in A3 has fewer characters so LEN counts 23 instead of 26 and ends up 23-7 = 16 characters  so it would display Kennewick Store. As you copy it down, LEN will count the number of characters that comprise the various store names and then the formula will subtract the first 7 characters.  Pretty slick and definitely useful.

GETPIVOTDATA Function

I am in the process of updating my Pivot Table course and decided that I take another look at  the GetPivotData function.
GET PIVOT DATA is an Excel function that pulls data from your pivot table. I personally don’t use it  because if you hide a field you referenced in GETPIVOTDATA, the function stops working. Sometimes, just rearranging the fields in the table can impact the function as well. However, I did want you to be aware of it.
If you try to multiply a total in a pivot table, you will immediately see that Excel automatically references the GETPIVOTDATA function.  This is a problem because if you copy the formula,  you will get the same answer for the entire column or row.
In the example below, I opened Pivot_Rearrange.xlsx and removed the Product Name field from the Pivot Table.  Next I  clicked on cell C5, as I wanted to multiply B5 by 1.10 to estimate next year’s order amount by carrier.

Instead of showing =B5 it shows =GETPIVOTDATA(“Order Amount”,$A$3,”Ship Via”, “FedEx”)*1.10

Pivot Table Function GETPIVOT

 

 

Notice that A3 is treated as an absolute cell reference. If you copy the formula down,  you will get the same answer for all of the Ship Via carriers.  As a work around, I know many people who copy and paste the pivot table into another workbook if they want to do any math on the pivot table.  However, I found a great tip on the MrExcel.com website on how to avoid this problem.

  1. Go up the Options icon and click the drop-down arrow to the right of it.
  2. Uncheck Generate GetPivotData.

Pivot Table- GETPIVOT 2

 

 

 

Now, if you go back to cell C5 and recreate the formula, you will see  the formula bar now shows =B5*1.10. It will now copy down correctly. (Excel will not correct the GETPIVOTDATA formula so you need to delete it and then type in =B5*1.1.)

GETPIVOT

 

 

 

The best part is that once you uncheck it, Excel remembers so you do not have to keep unchecking the Generate GetPivotData icon every time you want to use a formula that references the pivot table.
This is an excerpt from my new Pivot Table Course that will be out the first week of June 2016.

AGGREGATE Function

The AGGREGATE Function

The AGGREGATE function is a very powerful function for summarizing an array of data.
It combines 19 different ways to summarize data into one function!
While it is a powerful function, my favorite reason for using AGGREGATE is its ability to SUM data with errors. 

The syntax for the AGGREGATE function is =AGGREGATE(function #, options, array).
The function #’s are listed below:

aggregate function

 

 

 

 

 

 

 

 

 

 

 

 

 

AGGREGATEOPTIONS

 

 

 

 

As you can see, your options permit you to ignore nested subtotals, hidden rows as well as error values.
One of my biggest pet peeves is trying to sum data that has error values.  The AGGREGATE function makes this easy.
Assume yousum column with errors have data in cell A1:A6, and that you want to sum it.
However, because there is an error value in A5, the SUM function will return an error as shown in the screenshot on the left.
You can easily work around this by using the AGGREGATE function.

 

 

In this example, I clicked in A7 and  typed =AGGREGATE(
AGGREGATE FUNCTIONS

  • A list of the various function #’s appears.  Note that if you elect to use the Insert Function dialog box, this handy list of functions does not appear.  We want to SUM the data, so function # 9 should be used, followed by a comma.

 

 

  • After the comma, a list of options is presented.
  • AGGREGATE DIALOG BOX OF OPTIONSIn this case, use option number 6, Ignore error values and place a comma after this.

 

 

 

 

  • Lastly, identify the array to be summed.  This will be accomplished by using the cursor to highlight A1:A6.
  • Close the parenthesis.
  • Press Enter.

AGGREGATE SYNTAX

 

 

 

 

 

 

The “Must Know” component of the AGGREGATE function is how to SUM data with errors.  It is a very powerful function, though.  You may find other uses for it in your professional assignments.

 

This is an excerpt from “Must Know Functions for CPAs”.

EOMONTH

Date Function
EOMONTH

EOMONTH screenshot

screenshot of a function dialog box

EOMONTH displays the serial number of the last day of the specified month.
You can use this to calculate maturity dates and due dates that occur on the last day of the month.
In the example below, I have 1/2/2016 in cell A1. In another cell, I have typed =EOMONTH(A1,2) which tells Excel to look at the date in cell A1 and display the serial date of the last day of two months out which is March 31, 2016.
I keep saying serial number. In the screenshot below it shows 42460 as the answer even though in the bottom of the dialog box you can see the Formula result is 3/31/2016. If you want to see the date format then you need to go to the number format and select Short Date or some other date format.
If I had typed =EOMONTH(A1,1) it would have displayed the serial number of one month out which would be February 29, 2016.  If I had typed =EOMONTH(A1,-1) then it would display 12/31/2015.

Ways to Sum Data with Errors

Excel Techniques to Sum Data with Errors

oops- signThere are times that you need to know the sum of numeric data even though there may be errors in it.  The most common of these error values are #DIV/0!, #NUM!, #N/A and #VALUE!.  #DIV/0! refers to a situation where Excel is trying to divide by zero or by a cell that is blank.  This is mathematically impossible, hence the error value.  The #NUM! error value refers to a situation where there is a problem with the underlying formula and the #VALUE! error value most often occurs when Excel is trying to perform a mathematical operation with text.
There are numerous functions and techniques in Excel to sum data with errors.  Some have been recently developed by Microsoft and are much easier to use than the older techniques.  We’re going to start with the oldest technique first, then progress to the newer, easier techniques so that we cover all the versions of Excel.
Oldest Technique – ISERROR
The ISERROR function in Excel evaluates a cell and returns TRUE if there is an error and FALSE if there is not an error.  Because you only have a TRUE value or a FALSE value to work with, this function must generally be paired with an IF function to be useful.  The IF function in Excel is structured so that there is a value if TRUE and a value if FALSE.
Here’s an example:  More…..

An Automated Alternative in Excel

Automated alternative to FlashFill (and the “Text to Columns” Wizard)
My Excel tip is not so much about a favorite “tip or trick” as it is about a favorite concept. Tools like FlashFill and the “Text to Columns” Wizard are neat and certainly have their place, but I prefer an automated approach when appropriate. I emphasize “when appropriate” because one should always exercise good professional judgment when determining the correct tools and course of action to solve problems or complete tasks.
There are two reasons for my preference for automation when appropriate. First, an automated process usually requires less cumulative effort in repetitive process situations. Second, an automated process often can be shifted to lower-skilled workers, freeing those with higher skills to do other tasks that cannot be so delegated.
Let’s look at an example. In the spreadsheet below, each employee name in column B is composed of the employee’s last name and first name, separated by a comma delimiter and a space. If this is a “one-off” situation (particularly one having only a few names), you could (and perhaps should) use FlashFill or the “Text to Columns” Wizard to separate the names into their individual components.
Let’s say, however, that this data must be collected and separated each month for a large number of employees. Let’s also say that MS-Access (or a similar tool) is not available, and you do not want (or know how) to script code in VBA. Nesting functions can be an excellent way to automate tasks in Excel and create some interesting results.
For our example, Excel doesn’t have a “reverse concatenate” or “split” function, so we’ll have to get creative. In column C, we have extracted the last name from column B by nesting the SEARCH function inside the LEFT function. In column D, we have used the same approach, but substituted the RIGHT function for the LEFT function. Because the formula cell references are relative, they easily adjust as we copy them to the remaining cells. For reference, the two formulas are included in columns F and G.excel screenshot

Now let’s explain what just happened, using cell B3 for our explanation. In column C, we used the left function to extract the leftmost characters of cell B3 for the Last Name.

The second parameter of the LEFT function is used to specify the number of characters to return. Here, we have let Excel calculate the correct number of characters to return by using the SEARCH function to return the position of the comma in the cell contents — but we’re not done. We don’t want the comma returned, so we must adjust the calculated number of characters to exclude it.
Now we want to extract the First Name from cell B3. To do this, we use a combination of the RIGHT, SEARCH, and LEN functions.
Here, too, we allow Excel to provide the correct number of characters for the second parameter of the primary function. Conceptually, we will simply subtract the Last Name from cell B3 to arrive at the First Name.
However, we have a problem. We know the width of column B, but we don’t know the length of the content in each cell in column B without doing a lot of manual counting.
The answer? Let Excel figure it out, using the LEN function. The resulting formula tells Excel to subtract the leftmost characters from the cell content starting from (and excluding) the comma, and return the remaining (rightmost) characters. Once again, we exclude the comma from the results.
In actual fact, we should be asking Excel to exclude both the comma and the space (rather than the comma alone) from the results in both formulas. However, Excel is smart enough to trim the leading and trailing spaces from the results.
How can we set this monthly task up for delegation to an employee with lower skills? One way would be to house the employee names in one spreadsheet (or workbook) and house the formulas in another. Assuming data updates cannot be automated, the lower-skilled employee would simply copy or re-key the data into the specified position of the designated worksheet and the formulas would do the rest.

This tip is a guest blog post  from L.Keith Jordan, CPA
The LEN function in particular is totally underutilized. The LEN function in particular is very underutilized. If you want some other examples of what to do with it check out my Excel-Diva blog post on LEN.

 

The N() function. Comments Anyone?

Most people are familiar with Insert>Comment. Just right-click on a cell and select Insert>Comment. Very easy. Right-click on the comment to display it, edit it or delete it. You know there is a comment in a cell because of the red triangle in the upper right corner of the cell.
Comments

Using comments can be a pain as it is additional step and if you display the comment sometimes it can obscure other cells. A more interesting way to document formulas, in particular, is to use the N() function.
The N(item) function returns a zero when the item is a string so if I typed =Count(A1.A5)+N(“I am only counting numbers”) then the answer would be 2.=Count(A1.A5)=2+0 =2
N function This can be a nice quick way to provide information if you are creating a complex formula and want to remember what you are doing.

In the example below, I identified that I was looking up Unit Price based upon the Product ID   Now, if I had looked up Product Name instead of Unit Price, I would have received an error message if I had included the N() function. Can you guess why?  In that case instead of the resulting formula being 17.95+0=17.95 the formula would have been = “Bing Cherry”+0 =#VALUE!   You can’t add text and numbers together.  So, this may be useful, to some of you, if you use complex formulas and are always scratching your head wondering what the heck it says.N function with Vlookup

Computing a Loan Payment & Creating an Amortization Schedule

Computing the Periodic Payment for a Loan

Excel Financial Function

Computing the periodic payment is probably the most used Excel financial function simply because it is something everyone uses.
Let’s walk through an example.
You want to purchase a car and finance $24,000 over 24 months at an interest rate of 4%.   So, the question is –What is the monthly payment?
Here we have a value today (Pv) of $24,000.  We want a monthly payment for 24 months (Nper) at an annual interest rate of 4%.
First, we need to change the annual interest rate to a monthly one by dividing 4% by 12 = .0033333  Let’s reflect this in our worksheet.
Matching the rate to the payment frequency is important.  If you want to compute a monthly payment and have an annual rate, the rate must be divided by 12.  (So many people forget this little fact which is why I am really emphasizing it here.)

Present Value calculation
Click on cell B5, then click on the Fx button at the top left of the formulas tab.
At the top of the window that opens, select the category “Financial, then find and select PMT at the bottom.

Click OPMT-2K.

 

 

 

Fill in the cell references for Rate, Nper and PV as shown below: Rate is B3, Nper is B2 and PV is B1.
PMT-3
Click OK.
The monthly payment is $1,042.20.  Remember, since this was a monthly payment, the annual interest rate was adjusted by dividing it by 12.Also, please note that the answer is negative as it is considered an outflow. Yes- you can tell the programmer was not a CPA 🙂  The easiest way to fix this is to go up to the formula bar and put a negative sign in front of PMT so that it looks like =-PMT(B3,B2,B1)

Let’s  double-check our work by making an amortization schedule.
An amortization schedule starts with the initial loan amount and progresses it forward using the monthly interest rate and computed monthly payment.

Start with the beginning balance of $24,000.  Add interest to this amount at 4% divided by 12 (=B1*.04/12).  Reflect the payment as a negative amount and sum across the row (B4:D4) for the ending balance.

PMT-5

The new beginning balance in B5 should be the result in E4 (the formula in B5 will be =E4).  Copy these down 23 rows (for a total of 23 periods).  The ending balance should be very close to zero, but may not be exactly zero due to rounding (the use of only two decimal points in the payment).

amortization schedule

This proves that the monthly payment for a loan of $24,000 over 24 months is correct and provides us with the interest and remaining balance at the end of each payment period.

Mortgages work identically except that the number of periods is generally longer.

 

Using Workday to Calculate Invoice Dates

This is just a quick tip as Excel is not necessarily that date friendly.
If you want to calculate an invoice date or a project completion date use the WORKDAY() function.
Select a cell and then select the Formula tab and then select Date& Time

The syntax is as follows:
–Start_date Required. A date that represents the start date. Make sure to use a date and not text
–Days Required. The number of nonweekend and nonholiday days before or after start_date.
A positive value for days yields a future date; a negative value yields a past date.
–Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates

In the example below, I selected cell D3 which is where I wanted the due date to display.  F1 represents the number of days the invoice is due after from the invoice date which is in cell B3.  My start date was B3 and the number of days was 30, as shown in F1. I opted not to include holidays which is optional.
The resulting answer is 42095 which is a serial number.  This is not incorrect – it is just not formatted as a date. 
 Depending on how you have formatted your spreadsheet you may need to format D3 to display the actual date rather than the serial number. 

workday()

I opted to set up F1 as an input cell and used absolute cell references so that if I had a series of invoices I could just copy the formula down. The advantage of the input cell is that if I decide to give everyone 45 days instead of 30 days, I only have to change the value in F1.

workdkay2

 

 


If you use dates a lot you may be interested in an entry on my Excel-Diva blog entitled Converting a Date to a Calendar Quarter.

 

 

 

Preventing Duplicate Entries using Data Validation in Excel

If you are setting up a spreadsheet for someone else to enter data this is a neat trick. You can use the Data Validation and the CountIF feature to check for duplicate entries and then not allow the user to enter the duplicate data.

Let’s assume that the data you want to check is in Column A.
Either select tpreventing duplicate entrieshe entire Column or select the cells in Column A where the data is going to be entered.
Click on the Data tab and select Data Validation from the Data Tools group

  • In Allow: dropdown box, Select Custom
  • In formula type =CountIF(A:A,A1)=1
  • Click OK.
  • Now go to  Column A and enter some data in a few cells and then try to enter a duplicate piece of data. Excel will pop up with a restricted dialog box and tell you to retry.

 

Caveats:
This works great for new data however if you select cells where there is already data, even duplicate data, Excel will just ignore it. Also, if someone copies and pastes data into Column A it will override the data validation.

If you did not want to select all of Column A but instead wanted to select cells A5:A50 then the formula would be =CountIF($A$5:$A$50,A5)=1. If you forget the absolute cell references it will not work properly.

 

 

 

 

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom