Get conditional MAXIMUM value is easy, but how to get condit

[pmdown]

Get conditional MAXIMUM value in C-column is very easy usig formula:

=SUMPRODUCT(MAX((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11)))

but when I need conditional MINIMUM value, formula:

=SUMPRODUCT(MIN((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11)))

returns always null - not realy minimum value !!!

How to calculate conditional MINIMUM ? Thanx.

 

[Aladin Akyurek]

On 2002-07-04 02:33, pmdown wrote:
Get conditional MAXIMUM value in C-column is very easy usig formula:

=SUMPRODUCT(MAX((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11)))

but when I need conditional MINIMUM value, formula:

=SUMPRODUCT(MIN((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11)))

returns always null - not realy minimum value !!!

How to calculate conditional MINIMUM ? Thanx.

You need indeed formulas that operates on arrays for these tasks. I'd suggest to switch in both cases to array-entered MAX and MIN formulas.

=MAX((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11))

=MIN(IF((A2:A11=kriteruim1)*(B2:B11=kriterium2),C2:C11))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

You can also use DMAX and DMIN.

=DMAX(A2:C11,3,E1:F2)

=DMIN(A2:C11,3,E1:F2)

where E1:E2 consists of the label in A1 and kriterium1 and F1:F2 of the label in B1 and
kriterium2.
This message was edited by Aladin Akyurek on 2002-07-04 03:12
This message was edited by Aladin Akyurek on 2002-07-04 03:31

 

[pmdown]

Thank You Alladin.

Array entered formulas are more slower than SUMPRODUCT formula. I thing if its possible to use SUMPRODUCT formula for my task...

 

[Aladin Akyurek]

On 2002-07-04 04:23, pmdown wrote:
Thank You Alladin.

Array entered formulas are more slower than SUMPRODUCT formula. I thing if its possible to use SUMPRODUCT formula for my task...

Lets take the whole one by one:

(1)

=SUMPRODUCT(MAX((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11)))

will not be faster than the array-entered

(2)

{=MAX((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11))}

The reason for this is twofold: A) SUMORODUCT operates on arrays just like an array-formula, probably a slight bit faster, but B) the SUMPRODUCT formula in (1) has two fuction calls while the array-formula in (2) just one.

Both formulas are built with Boolean terms/subexpressions: the evaluation of these terms leads to arrays of 1's and 0's which multiplied with the range C2:C11 causes additional zeroes in the resulting array constant. That's when the trouble arises with MIN. What you need is that MIN gets applied to C2:C11 from which all numbers that do not meet the conditions are eliminated, not represented by a bunch of zeroes. This is the reason why you need IF, the Holy filterer:

{=MIN(IF((A2:A11=kriteruim1)*(B2:B11=kriterium2),C2:C11))}

You could of course wrap the IF((A2:A11=kriteruim1)*(B2:B11=kriterium2),C2:C11)) bit in a SUMPRODUCT. But, What is the point as it is necessary to array-enter the resulting formula?

Aladin

 

[Juan Pablo González]

On 2002-07-04 04:23, pmdown wrote:
Thank You Alladin.

Array entered formulas are more slower than SUMPRODUCT formula. I thing if its possible to use SUMPRODUCT formula for my task...

Well, I have to say, that you did in fact got the answer from one of the best formula *makers* I have EVER met...

You can use SUMPRODUCT, but with a combination of more formulas, which, just as Aladin said, would only make the formula slower.

For example, I did this formula, and I DID have to array enter it for it to work (But it uses SUMPRODUCT...)

{=SUMPRODUCT(MIN(IF(($B$1:$B$6=$B$8)*($C$1:$C$6=$B$9),$A$1:$A$6)))}

So, if you need speed, use the DB functions.

 

[Yogi Anand]

Hi pmdown:

You have several options now from our experts Aladin and Juan. You can also use the follwing nested if array formulas:

=MAX(IF(A2:A11=kriterium1,IF(B2:B11=kriterium2,C2:C11)))
and
=MIN(IF(A2:A11=kriterium1,IF(B2:B11=kriterium2,C2:C11)))

Regards!

 

[Aladin Akyurek]

On 2002-07-05 11:52, Yogi Anand wrote:
Hi pmdown:

You have several options now from our experts Aladin and Juan. You can also use the follwing nested if array formulas:

=MAX(IF(A2:A11=kriterium1,IF(B2:B11=kriterium2,C2:C11)))
and
=MIN(IF(A2:A11=kriterium1,IF(B2:B11=kriterium2,C2:C11)))

Regards!

I consider your post (the introductory sentence notwithstanding) superfluous on two counts:

1. The array option how to compute a conditional maximum and a conditional minimum has been already suggested to pmdown, the original poster:


=MAX((A2:A11=kriteruim1)*(B2:B11=kriterium2)*(C2:C11))

=MIN(IF((A2:A11=kriteruim1)*(B2:B11=kriterium2),C2:C11))

in a form that minimizes the number of function calls in the respective formulas.

What you come up with isn't another option at all -- the same thing but in a form that maximizes the number of function calls, worsening the performance;

2. And, it ignores completely the fact that the original poster has been also advised to use for better performance:



=DMAX(A2:C11,3,E1:F2)

=DMIN(A2:C11,3,E1:F2)

where E1:E2 consists of the label in A1 and kriterium1 and F1:F2 of the label in B1 and
kriterium2.

 

[Juan Pablo González]

Yes, Yogi is just trying to catch me in number of posts :wink: