Forum Discussion
Solved
IF Statement for Multiple conditions with ± results
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?
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!
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, .. .)?
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]
