Applying specific returns using IF, AND & OR when cells in a range have changing values.

[Denny57]

I am trying to find a formula that will return defined cell values when the values of cells in a given range have changing values.

The example returns the message "Too Many Arguments". I have tried a few variations but always with the same result

=IF(AND(B4="Win",D43>1,OR(C24="NR",D24="NR",E24="W")),W6,IF(AND(B4="Win",D43>1),OR(C24="NR",D24="W",E24="NR")),W5,IF(AND(B4="Win",D43>1),OR(C24="W",D24="NR",E24="NR")),W4),B3)

Cells B4 & D43 have constant values for each combination so perhaps can be included only once
The Range is C24:E24 In this instance 3 different combination of values "W" and "NR"
Combination 1 if TRUE = Value Cell W6
Combination 2 if TRUE = Value Cell W5
Combination 3 if TRUE = Value Cell W4
If ALL combinations FALSE = Value Cell B3

I will also need to be able to expand the formula to include a range of 5 cells generating up to 25 different combinations with up to 5 instances of NR.

Also, this is to find a solution where there are a minimum of 2 instances of "NR" in each combination.
If the formula was to include just one instance of NR the the number of combinations would significantly increase and each combination would require a specific cell value when TRUE. I already have a solution to this so this would only be a "Nice To Have " option if a concatenated option is available.

Any help would be grateful received

 

[etaf]

you have some brackets in the wrong place - so a correction to your formula would be
You are closing the bracket after the AND - BUT that needs to be removed so the OR will take place

=IF(AND(B4="Win",D43>1,OR(C24="NR",D24="NR",E24="W")),W6,IF(AND(B4="Win",D43>1,OR(C24="NR",D24="W",E24="NR")),W5,IF(AND(B4="Win",D43>1,OR(C24="W",D24="NR",E24="NR")),W4,B3)))

 

[etaf]

can you lay out the combinations
Providing
B4="Win",D43>1
Combination 1 if TRUE = Value Cell W6 - OR(C24="NR",D24="NR",E24="W")
Combination 2 if TRUE = Value Cell W5 - OR(C24="NR",D24="W",E24="NR"))
Combination 3 if TRUE = Value Cell W4 - OR(C24="W",D24="NR",E24="NR"))
If ALL combinations FALSE = Value Cell B3

IF ONLY C24 is "NR" - in your OR cases, then Its TRUE for both W6 OR W5

I think the rules need setting out more

 

[Denny57]

=IF(AND(B4="Win",D43>1,OR(C24="NR",D24="NR",E24="W")),W6,IF(AND(B4="Win",D43>1,OR(C24="NR",D24="W",E24="NR")),W5,IF(AND(B4="Win",D43>1,OR(C24="W",D24="NR",E24="NR")),W4,B3)))

I have tried the formula with all the combinations but unfortunately it appears that this is returning the first TRUE value for each. I was expecting different return values for each combination.

 

[etaf]

The first combination
=AND(B4="Win",D43>1,OR(C24="NR",D24="NR",E24="W"))
So it MUST have B4="Win",D43>1
BUT if ANY of the following 3 values exist -
OR(C24="NR",D24="NR",E24="W")
Then it will be TRUE
As mentioned in last POST
you can have TRUE for a few of those combinations and the first one will be considered

Combination 1 if TRUE = Value Cell W6 - OR(C24="NR",D24="NR",E24="W")
Combination 2 if TRUE = Value Cell W5 - OR(C24="NR",D24="W",E24="NR"))
Combination 3 if TRUE = Value Cell W4 - OR(C24="W",D24="NR",E24="NR"))

Lest say C24 is = "NR"
then it will always be the first if W6 and NOT the 2nd W5
even though in your example
if C24 = NR and nothing in the Other 2 cells
then its TRUE
and so W6 & W5 applies
BUT it does the IF in the order left to right

I think you may need to look at the combinations - as in most cases 2 are true

 

[Denny57]

Apologies for the last reponse this was sent in error

There is no set position for any of the values nor is there any definitive count of each value in a range. Nor is the range set and will be different for subsequent uses.

Each combination is unique and subsequently has a unique return value

B4="Win" might be able to be omitted

I need to change the D43>1 to check that the instances of NR in each range >1

However, each different combination returns a different unique value
So each return value is checking there is more than one instance of "NR" in the range, IF TRUE then
a) Determine which of the combinations is TRUE and return the value of the unique cell reference applied to that combination
d) Ignore those combinations that do not match the range
If FALSE, return " " or 0

So what I am trying to achieve in an IF statement is

IF the COUNT of NR in a range is greather than 1 AND (C24 ="W", D24 ="NR", E24 = "NR") is TRUE then RETURN the value of W6, OR,
IF the COUNT of NR in a range is greather than 1 AND (C24 ="NR", D24 ="W", E24 = "NR") is TRUE then RETURN the value of W5, OR,
IF the COUNT of NR in a range is greather than 1 AND (C24 ="NR", D24 ="NR", E24 = "W") is TRUE then RETURN the value of W4, OR,
IF the COUNT of NR in a range is greather than 1 AND (C24 ="NR", D24 ="NR", E24 = "NR") is TRUE then RETURN the value of B4, OR
IF the COUNT of NR in a range is less than 2 RETURN " " or 0.

No cells or their values are fixed and will change in subsequent uses.
There will be an instance where the range will contain 5 cells and there will be 25 possible combinations to be included and checked.

It is the syntax of the formula that I need to get sorted.

 

[etaf]

Ok, thanks , thats much different as you have an AND for the 3 values rather than an OR
whats the range to count NR
a countif( range , "NR") >1
would do that for you

So
IF ( AND ( C24 ="W", D24 ="NR", E24 = "NR" , COUNTIF ( RANGE - whatver that is, "NR") > 1

BUT we could test the range first
However you have count is greater than 1 - then later you say less than 2

=IF ( COUNTIF ( RANGE , "NR") < 2 , 0, IF (
Now we know the countif or the range is NOT less then 2 so must be 2 or more
so we can do the other IF

=IF ( COUNTIF ( RANGE , "NR") < 2 , 0, IF ( AND (C24 ="W", D24 ="NR", E24 = "NR"), W6 ,
IF ( AND (C24 ="NR", D24 ="W", E24 = "NR") , W5,
IF ( AND (C24 ="NR", D24 ="NR", E24 = "W"), W4,
IF ( AND (C24 ="NR", D24 ="NR", E24 = "NR"), B4 , 0
))))

No cells or their values are fixed and will change in subsequent uses.
There will be an instance where the range will contain 5 cells and there will be 25 possible combinations to be included and checked.

So we may have to look at a different way - perhaps a TABLE lookup, with an indirect()
BUT if the ranges are changing - then that maybe difficult

 

[Denny57]

Ok, thanks , thats much different as you have an AND for the 3 values rather than an OR
whats the range to count NR
a countif( range , "NR") >1
would do that for you

So
IF ( AND ( C24 ="W", D24 ="NR", E24 = "NR" , COUNTIF ( RANGE - whatver that is, "NR") > 1

BUT we could test the range first
However you have count is greater than 1 - then later you say less than 2

=IF ( COUNTIF ( RANGE , "NR") < 2 , 0, IF (
Now we know the countif or the range is NOT less then 2 so must be 2 or more
so we can do the other IF

=IF ( COUNTIF ( RANGE , "NR") < 2 , 0, IF ( AND (C24 ="W", D24 ="NR", E24 = "NR"), W6 ,
IF ( AND (C24 ="NR", D24 ="W", E24 = "NR") , W5,
IF ( AND (C24 ="NR", D24 ="NR", E24 = "W"), W4,
IF ( AND (C24 ="NR", D24 ="NR", E24 = "NR"), B4 , 0
))))


So we may have to look at a different way - perhaps a TABLE lookup, with an indirect()
BUT if the ranges are changing - then that maybe difficult

Etaf
Many thanks, the formula works as I had hoped. Unfortunately as evey combination is unique then look up tables are not an option.

Thanks again

 

[etaf]

you are welcome