A really interesting new feature in Excel 2010 is the AGREGAT () function, With which, among other things, the second smallest or second largest value can be found from a matrix or column. Compared to the old function SMALLEST () or LARGE () is new that with AGGREGAT () certain error or empty values such as # DIV / 0! let ignore.
You now have one Column, which was generated by a formula entry, and Cells with the mistake # DIV / 0! below, it can be processed as follows:
1. SMALLEST ()
Until now, the function of such a column was known SMALLEST (Matrix; [k]) just the following Result:
2. AGGREGATE ()
With the new function AGGREGATE () in Excel 2010 you now have the option to search through a matrix Error- or Blank values zu to ignore.
AGGREGAT (function; options; array; [k])
Already at Enter the function opens automatically Input assistant. With the Value 15, let's start with the function SMALLEST link with AGGREGAT ().
As you can see here known Features with AGGREGAT () to link.
Next up are those options on, which make the function so convenient. Again stands a Input assistant are available to support you.
Our preview of input des Worth 6, can be found when browsing the matrix Error values such as. # DIV / 0! to ignore.
An third and fourth Position of the functions are then still as usual the Matrix and the k value entered. For the second smallest Worth here is one k 2 To be awarded.
The Difference of both Features can be clearly seen here. While at the old Other specifications or than nested Functions for a correct output are necessary, gives the new Other specifications rinse the desired Value .
Have fun trying it out!
| Transparency: | This article may contain affiliate links. These will take you directly to the provider. If you make a purchase through one of these links, we will receive a commission at no extra cost to you. These links help us to refinance the operation of win-tipps-tweaks.de. |
This tip comes from www.win-tipps-tweaks.de
© Copyright Michael Hille
