Using Max Min with Array Formula issues with negative numbers

[Greg]

Hi,

I am getting some strange results and could use some help with the
proper logic and syntax to solve my problem. Essentially I am trying
to find the max and min % discount in a sub group of data.

My formula looks like this:

{Max((condition 1)*(condition 2)*(data colum))}

and

{Min((condition 1)*(condition 2)*(data colum))}

With the following data I get -35% returned for min (makes sense), but
get a zero for Max

-35%, -25%, -15%

So I thought I could just make the data points all abs and then find
what I want and multiply by -1, but alas sometimes my data looks like
this

-35%, -25%, 15%

In this case the max discount (smallest number) is -35%, but the
smallest discount is not a discount it is a premium to the price of
+15%. In this lower example my Min gives me -35% and Max gives me
+15%, which is all good. But if there is no positive number as in
first example, then as stated above the Max returns 0 versus the -15%

Any thoughts out there?

TIA,
Greg

 

[Harlan Grove]

I am getting some strange results and could use some help with the
proper logic and syntax to solve my problem. Essentially I am trying
to find the max and min % discount in a sub group of data.

My formula looks like this:

{Max((condition 1)*(condition 2)*(data colum))}

and

{Min((condition 1)*(condition 2)*(data colum))}

With the following data I get -35% returned for min (makes sense), but
get a zero for Max

-35%, -25%, -15%

So I thought I could just make the data points all abs and then find
what I want and multiply by -1, but alas sometimes my data looks like
this

-35%, -25%, 15%

And if you had 3 premiums and no discounts, your MIN formula would be fubar.

In this case the max discount (smallest number) is -35%, but the
smallest discount is not a discount it is a premium to the price of
+15%. In this lower example my Min gives me -35% and Max gives me
+15%, which is all good. But if there is no positive number as in
first example, then as stated above the Max returns 0 versus the -15%

Any thoughts out there?

There are times when you *SHOULD* use IF. This is one of them. Replace your
array formulas with the array formulas

=MAX(IF((condition 1)*(condition 2),data colum))

and

=MIN(IF((condition 1)*(condition 2),data colum))

 

[Greg]

Hi Harlan,

Thanks for the response.

The premium situation does not happen often and I would never have all
premiums with no discounts.

My condition1 and condition2 are to identify a subset of the full data
set. So your suggestion of adding an "if" statement makes sense, but I
would need to apply the logic to the data column.

I was hopping that there was an easy work around for the formula that
is returning a zero instead of the -15%. I suppose I need to check if
there are any positive numbers in the set and if there are none
present, I can use the "abs" solution. If there are positive numbers
then change appropriately.

This logic will make my formula real big and I was hopping for a more
elegant solution.

 

[Greg]

Ok,

I tried to build in some "if" logic to use "abs" but am still getting a
zero return when there is no zero in the data set!!!! arghhh..

I put in 1 and 0 flags to ensure the "if" statement is working and
there are no problems there. I then stripped out the true versus falst
portions and found that this is the one causing the problems. It
returns a zero versus a -1.2%. Also tried putting the ABS up at the
front of the formula to no avail.

{=MIN((SCFYVolume>=Overview!B12)*(SCFYVolume<=Overview!C12)*ABS('YTD
Spec Corp'!$F$4:$F$432))}


Any thoughts out there?

Greg

 

[Harlan Grove]

I tried to build in some "if" logic to use "abs" but am still getting a
zero return when there is no zero in the data set!!!! arghhh..

And you'll continue to get zero results until you use a technique that
returns something other than zeros when your criteria aren't satisfied.

I put in 1 and 0 flags to ensure the "if" statement is working and
there are no problems there. I then stripped out the true versus falst
portions and found that this is the one causing the problems. It
returns a zero versus a -1.2%. Also tried putting the ABS up at the
front of the formula to no avail.

{=MIN((SCFYVolume>=Overview!B12)*(SCFYVolume<=Overview!C12)*ABS('YTD
Spec Corp'!$F$4:$F$432))}

Any thoughts out there?

Is there any good reason that wouldn't reflect badly on you why you seem
unwilling to consider using the IF *FUNCTION*, as in

=MIN(IF((SCFYVolume>=Overview!B12)*(SCFYVolume<=Overview!C12),
'YTD Spec Corp'!$F$4:$F$432))

?

Just try the array formula immediately above. If you refuse to use the IF
function for this sort of thing, you'll never find an elegant solution to
your problem.

 

[Greg]

Harlen,

I suppose I was using the "If" statement withing the array formula
incorrectly. It now works.

Thanks,

Greg