Matt has attended:
Access Introduction course
Excel VBA Introduction course
Max If formula - non-array
Hi,
I'm trying to create a formula which returns the maximum value against a name (each name can have multiple values assigned).
E.g.
Matt - 1
Matt - 3
John - 2
John - 1
Steve - 4
Steve - 2
My desired outcome is
Matt = 3
John = 2
Steve = 4
Essentially it is a maxif formula but the one I used was an array which damaged the performance of the sheet.
Surely there is a non-array formula for this?
Many thanks,
Matt
RE: Max If formula - non-array
Hi Matt
Interesting question. There is no Maxif function in Excel. If the array formula effects performance there are other ways. Separate your data into columns with headings then use
Data, Subtotal choosing Max as the function.
Click the 2 button at the top left to display:
Name Number
John Max 2
Matt Max 3
Steve Max 4
This is a good method if there are many different names to subtotal.
Doug Dunn
Best STL
RE: Max If formula - non-array
Thanks Doug, didn't completely follow your response so I've gone for the lazy option and used a pivot returning max value.
You'd think excel would have a simple maxif function built, sumif and averageif must be some of the most used formulas.
Idiot question whilst I'm on - why do arrays cause memroy issues?
RE: Max If formula - non-array
Hi Matt
Your Pivot table solution in a good way. It's not so clear why arrays cause memory issues. Unlike with built in functions Excel probably reserves an area of memory to perform the calculation. The issues seem to arise when there are several arrays in one workbook.
Doug
Best STL
