Colin Legg
MrExcel MVP
- Joined
- Feb 28, 2008
- Messages
- 3,507
- Office Version
- 365
- Platform
- Windows
My line of thinking was just like yours, #NAME? - I thought MAX/MIN IF(S) would be par for the course in XL 2010, but no joy.
I think the AGGREGATE function will be handy. It essentially seems to perform the task of several existing functions (MAX, MIN, AVERAGE etc... you choose which one) with the additional option of ingnoring particular types, hidden rows, etc...
A brief synopsis from the helpfile:
It has two syntax forms:
The Options numbers available include:
So a simple example:
Range A1:A4 contains 1;[blank];#N/A;3
To get the MAX of 3, one could use:
I think the AGGREGATE function will be handy. It essentially seems to perform the task of several existing functions (MAX, MIN, AVERAGE etc... you choose which one) with the additional option of ingnoring particular types, hidden rows, etc...
A brief synopsis from the helpfile:
It has two syntax forms:
Some of the more common function_num available are:
(the complete set is 1 to 19).
The Options numbers available include:
(the complete set is 1 to 7).
So a simple example:
Range A1:A4 contains 1;[blank];#N/A;3
To get the MAX of 3, one could use:
Code:
=AGGREGATE(4,6,A1:A4)
