SOLVED

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
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
Highlighted

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

 

Highlighted

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

Highlighted

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

 

Highlighted

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

Highlighted

Select cell J3 and press key F2.

Do you notice something?

 

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