• 409K Members
• 6,276 Online
• 466K Conversations
SOLVED

## IF Statement for Multiple conditions with ± results

Highlighted
Occasional Contributor

# 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?

6 Replies

# Re: IF Statement for Multiple conditions with ± results

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","")`

# Re: IF Statement for Multiple conditions with ± results

@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, .. .)?

# Re: IF Statement for Multiple conditions with ± results

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

# Re: IF Statement for Multiple conditions with ± results

@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]

# Re: IF Statement for Multiple conditions with ± results

Select cell J3 and press key F2.

Do you notice something?

Solution

# Re: IF Statement for Multiple conditions with ± results

@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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies