SOLVED
Home

IF Statement for Multiple conditions with ± results

%3CLINGO-SUB%20id%3D%22lingo-sub-177724%22%20slang%3D%22en-US%22%3EIF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-177724%22%20slang%3D%22en-US%22%3E%3CP%3EI%20run%20photo%20competions%20for%20my%20camera%20club%20%7Bnot%20for%20profit%7D.%20Judges'%20scores%20must%20be%20within%20a%209%20point%20spread%20or%20they%20must%20rescore.%20I%20need%20this%20condition%20flagged%20for%20attention.%20So%2C%20the%20formula%20will%20look%20%22something%22%20like%20the%20following%3A%203%20judges%20in%20this%20case%20-%20Data%20cells%20A1%2C%20B1%2C%20and%20C1%20-%20and%20the%20results%20written%20to%20D1%20(contains%20the%20formula)%20would%20be%20the%20character%20%22X%22%20as%20a%20flag%20for%20the%20rescore%20condition%20being%20met.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIF%20A1%20-%20B1%20%E2%89%A5%20%C2%B110%20OR%20IF%20A2%20-C2%20%E2%89%A5%20%C2%B110%20OR%20IF%20B1%20-%20C1%20%E2%89%A5%20%C2%B110%20THEN%20D1%20%3D%20%22X%22%20%5Bwrite%20an%20%22X%22%20in%20D1%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20is%20this%20expressed%20in%20a%20formula%20that%20Excel%20can%20handle%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-177724%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178083%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178083%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3E%3CP%3ESelect%20cell%20J3%20and%20press%20key%20F2.%3C%2FP%3E%0A%3CP%3EDo%20you%20notice%20something%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%3DIF(MAX(F3%3AH3)-MIN(F3%3AH3)%26gt%3B10%2C%22X%22%2C%22%22)%26nbsp%3B%20-%26nbsp%3B%20Oh!%20should%20be%3C%2FP%3E%0A%3CP%3E%3DIF(MAX(F5%3AH5)-MIN(F5%3AH5)%26gt%3B10%2C%22X%22%2C%22%22)%20in%20J3%20-%26nbsp%3B%20All%20the%20cells%20are%20off%20as%20I%20started%20with%20F1%3AH1...%3C%2FP%3E%0A%3CP%3Ewhen%20it%20should%20have%20been%20F3%3AH3...%26nbsp%3B%20I%20read%20image%20numbers%2C%20column%20A%20insteads%20of%20row%20numbers%20in%20setting%20up%20the%20inital%20formula!%20It%20onlymarks%20conditions%20for%2011%20and%20above%2C%20though%20as%20it%20is%20so%20I%20hadto%20change%20the%2010%20to%20a%209.%20Now%2C%20all%20is%20work%20well.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178073%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178073%22%20slang%3D%22en-US%22%3E%3CP%3ESelect%20cell%20J3%20and%20press%20key%20F2.%3C%2FP%3E%0A%3CP%3EDo%20you%20notice%20something%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178049%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178049%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F133338%22%20target%3D%22_blank%22%3E%40William%20Curran%3C%2FA%3Ewrote%3A%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3E%3CP%3EWilliam%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20the%20difference%20between%20the%20lowest%20and%20the%20highest%20score%20is%20greater%20than%2010%20then%20show%20an%20X.%3C%2FP%3E%0A%3CPRE%3E%3DIF(MAX(A1%3AC1)-MIN(A1%3AC1)%26gt%3B10%2C%22X%22%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3EThank%20you%20for%20such%20a%20quick%20reply.%20Is%20the%20%22%26gt%3B10%22%20inclusive%20of%2010%20or%20just%20greater%20than%2010%20(11%2C%2012%2C%20..%20.)%3F%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3EMy%20implementation%20of%20the%20formula%20in%20the%20attached%20sheet%2C%3C%2FP%3E%0A%3CP%3E%3DIF(MAX(F1%3AH1)-MIN(F1%3AH1)%26gt%3B10%2C%22X%22%2C%22%22)%3C%2FP%3E%0A%3CP%3Eis%20making%20inexplicable%20errors%20(by%20me%20anyway).%20Meets%20conditions%20well%20for%20some%2C%20some%20are%20meet%20erroneously%2C%20and%20missed%20others%20entirely.%20The%20attached%20sheet%20uses%20the%20formula%20in%20column%20J.%20Column%20K%20indicates%20errors%20with%20a%20%22%3CSTRONG%3E!%3C%2FSTRONG%3E%22%20.%20%5BI%20tried%2010%20and%209%20in%20the%20formula.%20Both%20with%20poor%20results%20so%20I%20went%20with%20plan%20A%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178019%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178019%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20think%20that%20%3CSTRONG%3E%26gt%3B10%3C%2FSTRONG%3Ehas%20the%20same%20meaning%20all%20over%20the%20world%3A%20%3CSTRONG%3Egreater%20than%2010%3C%2FSTRONG%3E(excluding%2010).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178001%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178001%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3E%0A%3CP%3EWilliam%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20the%20difference%20between%20the%20lowest%20and%20the%20highest%20score%20is%20greater%20than%2010%20then%20show%20an%20X.%3C%2FP%3E%0A%3CPRE%3E%3DIF(MAX(A1%3AC1)-MIN(A1%3AC1)%26gt%3B10%2C%22X%22%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3EThank%20you%20for%20such%20a%20quick%20reply.%20Is%20the%20%22%26gt%3B10%22%20inclusive%20of%2010%20or%20just%20greater%20than%2010%20(11%2C%2012%2C%20..%20.)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-177728%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20for%20Multiple%20conditions%20with%20%C2%B1%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-177728%22%20slang%3D%22en-US%22%3E%3CP%3EWilliam%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20the%20difference%20between%20the%20lowest%20and%20the%20highest%20score%20is%20greater%20than%2010%20then%20show%20an%20X.%3C%2FP%3E%0A%3CPRE%3E%3DIF(MAX(A1%3AC1)-MIN(A1%3AC1)%26gt%3B10%2C%22X%22%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
William Curran
Occasional 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?

 

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!

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