Скачать или смотреть Excel Formula To Ignore Errors Or Hidden Rows - Episode 1211.111

Microsoft Excel Tutorial: Excel Formula To Ignore Errors Or Hidden Rows

Welcome back to the MrExcel netcast! In this episode, we will be diving into the depths of Excel with chapter 11 - AGGREGATE. As always, this podcast is sponsored by "MrExcel.com", making your Excel experience even easier.

Excel 2010 introduced a plethora of new functions, but one that stands out among the rest is AGGREGATE. This powerful function allows you to perform any of the 11 calculations found in the SUBTOTAL function, as well as some additional ones that were previously missing. These include MEDIAN, MODE, LARGE, SMALL, PERCENTILE, and QUARTILE.

One of the great features of the AGGREGATE function is its ability to ignore certain values or rows. Similar to SUBTOTAL, it can ignore other SUBTOTAL functions and hidden rows. But what sets AGGREGATE apart is its ability to also ignore error values. This can be extremely useful when working with large datasets that may contain errors.

To use the AGGREGATE function, you simply need to specify which of the 19 functions you want to use as the first argument, followed by the criteria for what you want to ignore as the second argument. This can be done by referencing a table that outlines which values to ignore for each number. And for those who love working with array formulas, you'll be happy to know that some of the arguments in AGGREGATE can be used in an array formula.

Let's take a look at an example. Say we have a range of values that includes an #N/A error. If we use the SUM function, the result will also be #N/A. However, if we use the AGGREGATE function with the argument 6 (which ignores error values), the #N/A will be excluded and we will get an actual total. This is just one of the many situations where AGGREGATE can come in handy.

So whether you need to add up numbers while ignoring hidden rows, other subtotals, or error cells, AGGREGATE has got you covered. It's a powerful tool that can make your data analysis and calculations much more efficient. Thank you for tuning in to this episode of the MrExcel netcast, and be sure to join us next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents
(00:00) AGGREGATE function debuts in Excel
(00:23) Many new functions, but AGGREGATE stands out
(00:42) 11 from Subtotal plus MEDIAN, MODE, LARGE, SMALL, PERCENTILE and QUARTILE
(01:24) Can ignore hidden or error rows
(01:51) Some calculations allow arrays
(02:09) Example in use
(02:41) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelnew #excelformula #excelformulasandfunctions

This video answers these common search terms:
how to use an aggregate formula in excel
how to ignore errors when summing in excel
how to skip hidden rows when summing in excel
excel formula to ignore errors or hidden rows
how to exclude errors or hidden rows in excel calculations
ignoring #n/a or hidden rows in excel calculations
conditional calculations in excel to skip errors or hidden rows
excluding error values or hidden rows in excel functions
ignoring error cells or filtered rows in excel calculations
bypassing errors or hiding rows in excel formulas
customizing calculations to omit errors or hidden rows in excel
excel formula for conditional calculations with error or hidden row

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

Excel 2010 adds a new function called AGGREGATE. This function is similar to SUBTOTAL but we several new tricks, including MEDIAN, LARGE, SMALL, and the ability to ignore error values