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

  • Thread starter Thread starter SK08
  • Start date Start date
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?
 
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
 
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?
Click to expand...

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?
 
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))
 
Rick Rothstein said:
=SUMPRODUCT(MAX((B1:B100=2)*(E1:E100)))
Click to expand...
....

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 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))))
Click to expand...
....

Slightly less ridiculous,

=LOOKUP(2,1/(E2:E31=MAX((B2:B31=Phase)*E2:E31+(B2:B31<>Phase)*MIN
(E2:E31))),E2:E31)
 
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?)
 
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?)
Click to expand...

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.
 
Back
Top