Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function not working correctly if I use an "or - statement"
Bob,
Your suggestion works perfectly. Many thanks!! /Peder |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use "offset" function in "array formula"? | Excel Discussion (Misc queries) | |||
embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions | |||
Hashing function CryptAcquireContext Lib "advapi32" not working in new computer. | Excel Programming | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |