Microsoft Office 2010 is for everyone, so is AGGREGATE() from Excel 2010‏

Over the last few weeks, We have been exploring a couple of new features in Excel 2010. Among a few features that caught my attention like Sparklines, improvised SmartArt, more conditional formatting options, Power Pivot etc. is the new Aggregate function.

Aggregate function is of great interest to me because, most of my Excel spreadsheets have arrays or ranges that need a SUBTOTAL, COUNT or a COUNTA. These functions might have a hidden or a missing, or an error field; this results in my final cell that contains my final result in the very notorious #ERROR or #VALUE! error if I were to use the SUM function for example. I have always wanted a workaround for this problem without having to manually exclude the cells in my data range while Excel computes or applies math functions; gladly, that is now built right into Microsoft Excel 2010

Below is an example of how the AGGREGATE function will make your tables cleaner, this sheet has data sources that get updated by various sales divisions and if any of them has an incomplete or erroneous cell it gives an error in my final result.

The total sales does not show up when I use the SUM function in Row 15, but AGGREGATE function comes to my rescue I use it in Row 17 and set it to ignore all errors in all cells that fall in the range.

Here’s how I get to see it in action.

Try this out for yourself and I also encourage you to learn more about Excel functions at http://office.microsoft.com/en-us/excel-help/CH006252829.aspxto realize your full potential.