SOLVED

IF Statement for Multiple conditions with ± results

Copper Contributor

I run photo competions for my camera club {not for profit}. Judges' scores must be within a 9 point spread or they must rescore. I need this condition flagged for attention. So, the formula will look "something" like the following: 3 judges in this case - Data cells A1, B1, and C1 - and the results written to D1 (contains the formula) would be the character "X" as a flag for the rescore condition being met.

 

IF A1 - B1 ≥ ±10 OR IF A2 -C2 ≥ ±10 OR IF B1 - C1 ≥ ±10 THEN D1 = "X" [write an "X" in D1]

 

How is this expressed in a formula that Excel can handle?

6 Replies

William,

 

if the difference between the lowest and the highest score is greater than 10 then show an X.

=IF(MAX(A1:C1)-MIN(A1:C1)>10,"X","")

 


@Detlef Lewin wrote:

William,

 

if the difference between the lowest and the highest score is greater than 10 then show an X.

=IF(MAX(A1:C1)-MIN(A1:C1)>10,"X","")

 


Thank you for such a quick reply. Is the ">10" inclusive of 10 or just greater than 10 (11, 12, .. .)?

I would think that >10 has the same meaning all over the world: greater than 10 (excluding 10).

 


@William Curran wrote:

@Detlef Lewin wrote:

William,

 

if the difference between the lowest and the highest score is greater than 10 then show an X.

=IF(MAX(A1:C1)-MIN(A1:C1)>10,"X","")

 


Thank you for such a quick reply. Is the ">10" inclusive of 10 or just greater than 10 (11, 12, .. .)?


My implementation of the formula in the attached sheet,

=IF(MAX(F1:H1)-MIN(F1:H1)>10,"X","")

is making inexplicable errors (by me anyway). Meets conditions well for some, some are meet erroneously, and missed others entirely. The attached sheet uses the formula in column J. Column K indicates errors with a "!" . [I tried 10 and 9 in the formula. Both with poor results so I went with plan A]

Select cell J3 and press key F2.

Do you notice something?

 

best response confirmed by William Curran (Copper Contributor)
Solution

@Detlef Lewin wrote:

Select cell J3 and press key F2.

Do you notice something?

 


=IF(MAX(F3:H3)-MIN(F3:H3)>10,"X","")  -  Oh! should be

=IF(MAX(F5:H5)-MIN(F5:H5)>10,"X","") in J3 -  All the cells are off as I started with F1:H1...

when it should have been F3:H3...  I read image numbers, column A insteads of row numbers in setting up the inital formula! It onlymarks conditions for 11 and above, though as it is so I hadto change the 10 to a 9. Now, all is work well. Thank you!

1 best response

Accepted Solutions
best response confirmed by William Curran (Copper Contributor)
Solution

@Detlef Lewin wrote:

Select cell J3 and press key F2.

Do you notice something?

 


=IF(MAX(F3:H3)-MIN(F3:H3)>10,"X","")  -  Oh! should be

=IF(MAX(F5:H5)-MIN(F5:H5)>10,"X","") in J3 -  All the cells are off as I started with F1:H1...

when it should have been F3:H3...  I read image numbers, column A insteads of row numbers in setting up the inital formula! It onlymarks conditions for 11 and above, though as it is so I hadto change the 10 to a 9. Now, all is work well. Thank you!

View solution in original post