Is there a way to find the max in a set of data that meet a certaincriteria w/o using an array formu

[SK08]

I want to find the max value in a set of data that is also classified
as phase 1

column B has the phase that the data falls into and column E has the
value of which I want to find the max for. Is there any way to do this
without using an array formula?

 

[Bernard Liengme]

Have a look at the replies to the message from SCC at 3:26 yesterday
You will need to use an array formula unless a VBA solution is acceptable
best wishes

 

[Harlan Grove]

I want to find the max value in a set of data that is also classified
as phase 1

column B has the phase that the data falls into and column E has the
value of which I want to find the max for. Is there any way to do this
without using an array formula?

Yes, but it's rather ridiculous. For example,

=SUMPRODUCT(--((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31)
=MAX((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31))),E2:E31)
/SUMPRODUCT(--((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31)
=MAX((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31))))

So why can't you use array formulas?

 

[Rick Rothstein]

It depends on what you mean by "array formula". If you are referring only to those that require Ctrl+Shift+Enter to commit them (because you keep forgetting to do that perhaps), then you could use the SUMPRODUCT function to avoid that keystroke combination...

=SUMPRODUCT(MAX((B1:B100=2)*(E1:E100)))

where I'm using 2 as your Phase number. This formula requires only the Enter key to commit it; however, it does introduce an extra function call over this more efficient array-entered formula (that is, using Ctrl+Shift+Enter to commit)...

=MAX((B1:B100=2)*(E1:E100))

 

[Harlan Grove]

=SUMPRODUCT(MAX((B1:B100=2)*(E1:E100)))

....

If there were n > 1 instances of the MAX value for Phase = 2, your
formula above would return n times the MAX value.

 

[Harlan Grove]

Yes, but it's rather ridiculous. For example,

=SUMPRODUCT(--((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31)
=MAX((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31))),E2:E31)
/SUMPRODUCT(--((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31)
=MAX((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN(E2:E31))))

....

Slightly less ridiculous,

=LOOKUP(2,1/(E2:E31=MAX((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN
(E2:E31))),E2:E31)

 

[Rick Rothstein]

Are you sure? My tests do not show that. (Isn't the MAX function picking out the single largest value from the array of values that the SUMPRODUCT function forces it to look at?)

 

[Harlan Grove]

Are you sure? My tests do not show that. (Isn't the MAX function picking out
the single largest value from the array of values that the SUMPRODUCT function
forces it to look at?)

Good point. I misdiagnosed the problem. But you're not out of the
woods. If the largest column E value were negative, your formula would
likely return 0 rather than the true maximum.