Problems with MIN

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I can't figure out why this works below:

=MAX((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121),merged_stats!$G$1:$G$30500)

But this does not, yields 0 (and there is no zero value in column G
given these conditionals)

=MIN((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121),merged_stats!$G$1:$G$30500)

I want the min and max values given some section criterion

Thanks,

Frank
 
Hi
not fully tested but try
=MAX(IF(merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D
$121),merged_stats!$G$1:$G$30500))
and
=MIN(IF(merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D
$121),merged_stats!$G$1:$G$30500))

both are array formulas (to be entered with CTRL+SHIFT+ENTER)
 
=MAX(IF((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121
),merged_stats!$G$1:$G$30500))

=MIN(IF((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121
),merged_stats!$G$1:$G$30500))

Both formulas must be confirmed with control+shift+enter instead of just
with enter.
 
Your formula is equivalent to
=MAX(a,b)
where
a=MAX((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121))
b=MAX(merged_stats!$G$1:$G$30500)

In particular, column G enters unconditionally.

What are you trying to do?

Jerry
 
Hi

At first, for your formula having any meaning, it has to be an array formula
(entered as Ctrl+Shift+Enter) - you see such formula in formula bar in curly
brackets, like
={MAX((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121),
merged_stats!$G$1:$G$30500)}. Or you have to explayn what you want to do to
us.

Whenever any value in column G >1, the max value will be from column G,
because the value for
={(merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121)}
is either 0 (when there is no matching row in range 1:30500) or 1.

Nearly same for minimum formula. Unless in column G is at least one value <1
(or <0, when there is no match for 1st part of formula), the result will be
1 (or 0)

As for getting 0 as answer regardless values in your dataranges, maybe your
data are texts instead of numbers?
 
Hi

Then maybe array formula
={MAX((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121)*
(merged_stats!$G$1:$G$30500))}
 
That seemed to do the trick. Never knew you had to do

control+shift+enter. In what cases of a formula do you need to do that?
 
In what cases of a formula do you need to do that?

To start with: the Help file contains info on "About array formulas and how
to enter them".

That seemed to do the trick. Never knew you had to do

control+shift+enter. In what cases of a formula do you need to do that?

Aladin Akyurek wrote:

=MAX(IF((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121
),merged_stats!$G$1:$G$30500))

=MIN(IF((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121
),merged_stats!$G$1:$G$30500))

Both formulas must be confirmed with control+shift+enter instead of just
with enter.


I can't figure out why this works below:



=MAX((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121),m
erged_stats!$G$1:$G$30500)

But this does not, yields 0 (and there is no zero value in column G
given these conditionals)



=MIN((merged_stats!$C$1:$C$30500=F122)*(merged_stats!$A$1:$A$30500=$D$121),m
erged_stats!$G$1:$G$30500)

I want the min and max values given some section criterion

Thanks,

Frank
 
Back
Top