Max If formula - non-array

Resolved · Low Priority · Version 2007

Thu 28 Nov 2013, 11:50 Delegate Matt said...

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

Fri 29 Nov 2013, 10:06 Trainer Doug said...

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

Tue 3 Dec 2013, 11:10 Delegate Matt said...

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?

Fri 17 Jan 2014, 10:09 Trainer Doug said...

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

Fri 24 Jan 2014: Automatically marked as resolved.

Training courses