Category Archives: Misc Functions

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

 

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”.

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…..

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

  • I loved the course as it had current cases which I am familiar with. Best ethics course I have taken in recent memory. Thanks!”

    - Kathy

  • “I am extremely impressed with the courses and the reasonable price.” -Janet A. May 2017

    - Janet

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

    - Alex