From the course: Excel Tips Weekly

Unlock this course with a free trial

Join today to access over 22,600 courses taught by industry experts.

Use the AGGREGATE function to circumvent errors and ignore hidden data

Use the AGGREGATE function to circumvent errors and ignore hidden data - Microsoft Excel Tutorial

From the course: Excel Tips Weekly

Start my 1-month free trial Buy for my team

Use the AGGREGATE function to circumvent errors and ignore hidden data

“

- [Instructor] When you're trying to tabulate data 2 that might involve error cells, 3 and particularly if you don't have time 4 to fix those error cells, 5 a function to rely upon is likely 6 to be one called AGGREGATE, introduced back in Excel 2010. 7 In this worksheet, 8 I want to tabulate the total service years. 9 Column F has a series of formulas using DATEDIF 10 to calculate years of service based on today's date. 11 And as I'm recording this, it's August 14th of 2020. 12 So if you're looking at this data later, 13 you might see some different numbers there. 14 Anyway, we've got these answers. 15 I'd like to get a sum of them right here. 16 I'll press Alt + Equal, SUM, 17 click column F, Enter. 18 And I'm getting an answer that doesn't make a lot of sense. 19 I'm not sure if I have a problem yet. 20 I'll try this with AVERAGE. 21 Equal average, a parenthesis. 22 I'll click column F. 23 Same problem. 24…

Contents