Array function not working correctly if I use an "or - statement"
 

Hi,

I have two arrays which I want to use in order to create some
statistics. However, if I want EITHER of two things to hold true for
the array matching, then it breaks down (gives wrong value).

The following straightforward line works perfectly:

{=MIN(IF((NB1=$B$12),NB_LossAmount))}

However, this line (which should do the same) does not work:

{=MIN(IF(OR(NB1=$B$12),NB_LossAmount))}

Why is this discrepancy?

What I really want to do is the following...

{=MIN(IF(OR(NB1=$B$12,NB1=$B13),NB_LossAmount))}


I know I can multiply the two arrays together if I want to check that
NB_LossAmount for the ones where NB1 equals both B12 and B13, but this
is not what I want. I want to have it so that if NB1 equals EITHER B12
or B13, then take the value from NB_LossAmount..

Any suggestions greatly appreciated

Best regards,

Peder Wessel

Array function not working correctly if I use an "or - statement"
 

You code look correct. I tried it on Excel 2003. I would use the Evaluate
formula feature to determin the problem.

Menu tools - Formula Auditing - Evaluate Formula

"Wesslan" wrote:

Hi,

I have two arrays which I want to use in order to create some
statistics. However, if I want EITHER of two things to hold true for
the array matching, then it breaks down (gives wrong value).

The following straightforward line works perfectly:

{=MIN(IF((NB1=$B$12),NB_LossAmount))}

However, this line (which should do the same) does not work:

{=MIN(IF(OR(NB1=$B$12),NB_LossAmount))}

Why is this discrepancy?

What I really want to do is the following...

{=MIN(IF(OR(NB1=$B$12,NB1=$B13),NB_LossAmount))}


I know I can multiply the two arrays together if I want to check that
NB_LossAmount for the ones where NB1 equals both B12 and B13, but this
is not what I want. I want to have it so that if NB1 equals EITHER B12
or B13, then take the value from NB_LossAmount..

Any suggestions greatly appreciated

Best regards,

Peder Wessel

Array function not working correctly if I use an "or - statement"
 

{=MIN(IF((NB1=$B$12)+(NB1=$B13),NB_LossAmount))}

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Wesslan" wrote in message
...
Hi,

I have two arrays which I want to use in order to create some
statistics. However, if I want EITHER of two things to hold true for
the array matching, then it breaks down (gives wrong value).

The following straightforward line works perfectly:

{=MIN(IF((NB1=$B$12),NB_LossAmount))}

However, this line (which should do the same) does not work:

{=MIN(IF(OR(NB1=$B$12),NB_LossAmount))}

Why is this discrepancy?

What I really want to do is the following...

{=MIN(IF(OR(NB1=$B$12,NB1=$B13),NB_LossAmount))}


I know I can multiply the two arrays together if I want to check that
NB_LossAmount for the ones where NB1 equals both B12 and B13, but this
is not what I want. I want to have it so that if NB1 equals EITHER B12
or B13, then take the value from NB_LossAmount..

Any suggestions greatly appreciated

Best regards,

Peder Wessel

Array function not working correctly if I use an "or - statement"
 

Bob,

Your suggestion works perfectly. Many thanks!!

/Peder

Array function not working correctly if I use an "or - statement"
 

"Joel" wrote in message
...
You code look correct. I tried it on Excel 2003.

You did? I would suggest that your test data must have been highly dubious
then because ORing two conditions like that will return a singleton TRUE if
any row matches, or a singleton FALSE if none do. If it does resolve to
TRUE, the IF returns EVERY value in NB_LossAmount, and the MIN will only
return the (dubiously) correct answer if the minimum value of all values
happens to meet that set of conditions. If the minimum is in a row that
doesn't meet it, it will return that value.

To spell it out

NB1: X,X,X,X,Z,Y,Y,Z,Y,X
NB_LossAmount: 36,36,27,55, 8,41,29,5,72,61

If B12 = X and B13 = Y the minimum value that meets either condition is 27.
Your formula will return 5, the minimum value regardless because of the fact
that it build an array of all values, not just those that meet the criteria.