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

S

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?
 
B

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
 
H

Harlan Grove

SK08 said:
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?
 
R

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))
 
H

Harlan Grove

Rick Rothstein said:
=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.
 
H

Harlan Grove

Harlan Grove said:
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)
 
R

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?)
 
H

Harlan Grove

Rick Rothstein said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top