if statement - testing a single cell works, testing a range of cells not working

[jgold20]

The below works:

=IF(OR(I10>H10,I11>H11,I12>H12,I13>H13,I14>H14)," Discount Given is greater then Max. Discount",
IF(OR(C3={"B","W"}),"Discount can be UP to 7.5 %",
IF(OR(C3={"L","N"}),"Disc. Max. 3%/Midwest only - up to 7.5 %",
IF(OR(C3={"D","V","X","SR"}),"ENTER xx for MAX. DISCOUNT",""))))

When are try and do a range, I get a value error:

=IF(OR(I10>H10,I11>H11,I12>H12,I13>H13,I14>H14)," Discount Given is greater then Max. Discount",
IF(OR(C3:C7={"B","W"}),"Discount can be UP to 7.5 %",
IF(OR(C3={"L","N"}),"Disc. Max. 3%/Midwest only - up to 7.5 %",
IF(OR(C3={"D","V","X","SR"}),"ENTER xx for MAX. DISCOUNT",""))))

 

[Michael M]

Try using instead...

=IF(OR(COUNTIF(C3:C7,"B")>0,COUNTIF(C3:C7,"W")>0)

 

[JoeMo]

You need to do the OR test for each cell in the range. Assuming if any of C3:C7 is "B" or "W" you want the discount to be up to 7.5%, here's an alternative:

IF(SUMPRODUCT((C3:C7="B")+(C3:C7="W"))>0,"Discount can be UP to 7.5 %", ...

 

[jtakw]

Hi,

Try this:

=IF(OR(I10>H10,I11>H11,I12>H12,I13>H13,I14>H14),"Discount Given is greater then Max. Discount",IF(SUM(COUNTIF(C3:C7,{"B","W"})),"Discount can be UP to 7.5 %",IF(OR(C3={"L","N"}),"Disc. Max. 3%/Midwest only - up to 7.5 %",IF(OR(C3={"D","V","X","SR"}),"ENTER xx for MAX. DISCOUNT",""))))

 

[jgold20]

Problem just got a little more complicated. I have to possible handle all in the same way and a possible combination of all of them together plus 2 more combinations - if they exist with a different value in C3:C7 or if they do not exist at all but the different value does.


=IF(OR(I10>H10,I11>H11,I12>H12,I13>H13,I14>H14)," Discount Given is greater then Max. Discount",
IF(OR(C3:C7={"B","W"}),"Discount can be UP to 7.5 %",
IF(OR(C3:C7={"L","N"}),"Disc. Max. 3%/Midwest only - up to 7.5 %",
IF(OR(C3:C7={"D","V","X","SR"}),"ENTER xx for MAX. DISCOUNT",
IF(OR(C3:C7={"B","W""D","L","N"}),"Discount can be UP to 7.5 %",
IF(OR(C3:C7={"H","GE"}),"Max discount is 5%",
IF(OR(C3:C7={"B","W""D","V","X","SR"}),"Discount can be UP to 7.5 %/ENTER xx for MAX. DISCOUNT","")))). ETC

 

[Michael M]

wonder if you might better off with a table, where B =1, D =2, etc then reffering to the table
OR
maybe a select case statement in VBA might be a better option ????


BUT, we would need to know every single variable in the equation !

 

[jgold20]

The below is working EXCEPT if I have something else (C3=H,"MAX % is 5%", etc.) along with any of the other combinations:

=IF(OR(I10>H10,I11>H11,I12>H12,I13>H13,I14>H14)," Discount Given is greater then Max. Discount - CALL LISA OR JOE",
IF(SUMPRODUCT((C3:C7="B")+(C3:C7="W"))>0,"Discount can be UP to 7.5 %",
IF(SUMPRODUCT((C3:C7="L")+(C3:C7="N"))>0,"Disc. Max. 3%/Midwest only - up to 7.5 %",
IF(SUMPRODUCT((C3:C7="B")+(C3:C7="W")+(C3:C7="L")+(C3:C7="N"))>0,"Discount can be UP to 7.5 %",
IF(SUMPRODUCT((C3:C7="D")+(C3:C7="V")+(C3:C7="X")+(C3:C7="SR"))>0,"ENTER CF for MAX. DISCOUNT",
IF(SUMPRODUCT((C3:C7="L")+(C3:C7="N")+(C3:C7="D")+(C3:C7="V")+(C3:C7="X")+(C3:C7="SR"))>0,"Discount can be UP to 7.5 %/ENTER CF for MAX. DISCOUNT",
IF(SUMPRODUCT((C3:C7="B")+(C3:C7="W")+(C3:C7="L")+(C3:C7="N")+(C3:C7="D")+(C3:C7="V")+(C3:C7="X")+(C3:C7="SR"))=0,"Max % is 5%",)))))))