Display a text result based on numeric values

%3CLINGO-SUB%20id%3D%22lingo-sub-2379681%22%20slang%3D%22en-US%22%3EDisplay%20a%20text%20result%20based%20on%20numeric%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379681%22%20slang%3D%22en-US%22%3E%3CP%3ESeeking%20a%20formula%20for%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20P5%20is%20less%20than%200.9%2C%20display%20B5%20in%20Q5%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3EIf%20P5%20is%20greater%20or%20equal%20to%200.9%20but%20less%20than%20or%20equal%20to%201.1%2C%20display%20%22Even%22%20in%20Q5%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3EIf%20P5%20is%20greater%20than%201.1%2C%20display%20A5%20in%20Q5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20Bob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2379681%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379779%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20a%20text%20result%20based%20on%20numeric%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20P5%20is%20less%20than%200.9%2C%20display%20B5%20in%20Q5%3CBR%20%2F%3Ecannot%20be%20an%20OR%20but%20should%20be%20an%20ELSE%3A%3CBR%20%2F%3EIF(P5%26lt%3B.9%2CB5%3CBR%20%2F%3EIf%20P5%20is%20greater%20or%20equal%20to%200.9%20but%20less%20than%20or%20equal%20to%201.1%2C%20display%20%22Even%22%20in%20Q5%3CBR%20%2F%3Ecannot%20be%20an%20OR%20but%20an%20AND%3CBR%20%2F%3EIF(%3CBR%20%2F%3EAND(P5%26gt%3B%3D.90%2CP5%26lt%3B%3D1.1)%2C%22EVEN%22%2C%3C%2FP%3E%3CP%3Ecannot%20be%20an%20OR%20but%20an%20ELSE%20(assuming%20it%20is%20then%20greater%20than%201.1%20based%20on%20the%20IF%20statement%20above)%3C%2FP%3E%3CP%3EA5%3C%2FP%3E%3CP%3ESo%20to%20sum%20it%20all%20up%3A%3C%2FP%3E%3CPRE%3EIF(P5%26lt%3B.9%2C%20B5%2C%3CBR%20%2F%3E%20%20%20IF(%3CBR%20%2F%3E%20%20%20%20%20%20AND(%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20P5%26gt%3B%3D.9%2CP6%26lt%3B%3D1.1%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20)%2C%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%22EVEN%22%2C%20A5%3CBR%20%2F%3E%20%20%20%20%20)%3CBR%20%2F%3E%20%20)%3C%2FPRE%3E%3CP%3EPaste%20in%20Q5%20in%20excel%3A%26nbsp%3B%3C%2FP%3E%3CPRE%3EIF(P5%26lt%3B.9%2C%20B5%2CF(AND(P5%26gt%3B%3D.9%2CP6%26lt%3B%3D1.1)%2C%22EVEN%22%2C%20A5))%3C%2FPRE%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379785%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20a%20text%20result%20based%20on%20numeric%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379785%22%20slang%3D%22en-US%22%3E%3CP%3EUntested...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(P5%26lt%3B0.9%2CB5%2CP5%26lt%3B%3D1.1%2C%22Even%22%2CTRUE%2CB5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(P5%26lt%3B0.9%2CB5%2CP5%26lt%3B%3D1.1%2C%22Even%22%2CP5%26gt%3B1.1%2CB5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Seeking a formula for the following:

 

If P5 is less than 0.9, display B5 in Q5

OR

If P5 is greater or equal to 0.9 but less than or equal to 1.1, display "Even" in Q5

OR

If P5 is greater than 1.1, display A5 in Q5

 

Thank you, Bob

2 Replies

Hi @rbellotti 

If P5 is less than 0.9, display B5 in Q5
cannot be an OR but should be an ELSE:
IF(P5<.9,B5
If P5 is greater or equal to 0.9 but less than or equal to 1.1, display "Even" in Q5
cannot be an OR but an AND
IF(
AND(P5>=.90,P5<=1.1),"EVEN",

cannot be an OR but an ELSE (assuming it is then greater than 1.1 based on the IF statement above)

A5

So to sum it all up:

IF(P5<.9, B5,
IF(
AND(
P5>=.9,P6<=1.1
),
"EVEN", A5
)
)

Paste in Q5 in excel: 

IF(P5<.9, B5,F(AND(P5>=.9,P6<=1.1),"EVEN", A5))

Cheers

Untested...

=IFS(P5<0.9,B5,P5<=1.1,"Even",TRUE,B5)

or

=IFS(P5<0.9,B5,P5<=1.1,"Even",P5>1.1,B5)

@rbellotti