SOLVED

Excel formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2228299%22%20slang%3D%22en-US%22%3EExcel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2228299%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20I'm%20trying%20to%20do%3A%3C%2FP%3E%3CP%3ECell%20A1%20has%20a%20numeric%20value.%20In%20cell%20B1%2C%20I%20want%20to%20be%20able%20to%20do%20the%20following%3A%3C%2FP%3E%3CP%3EIf%20A1%20is%20%3D%26lt%3B12%2C%20enter%20the%20value%20in%20cell%20A1%20into%20cell%20B1%3C%2FP%3E%3CP%3EIf%20A1%20is%20greater%20than%2012%2C%20enter%2012%20into%20cell%20B1%3C%2FP%3E%3CP%3EMy%20formula%20is%3A%3C%2FP%3E%3CP%3E%3DIF((OR(E9%26lt%3B%3D12%2C%20E9%26gt%3B12))%2C%20E9%2C12)%3C%2FP%3E%3CP%3EWhat%20is%20wrong%20with%20my%20logic%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2228299%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2228325%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2228325%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%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20below%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(A1%26lt%3B%3D12%2CA1%2C12)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2228676%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2228676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%3A%20%60%60If%20A1%20is%20%3D%26lt%3B12%2C%20enter%20the%20value%20in%20cell%20A1%20into%20cell%20B1.%20If%20A1%20is%20greater%20than%2012%2C%20enter%2012%20into%20cell%20B1%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMIN(12%2C%20A1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%3A%20%60%60%3DIF((OR(E9%26lt%3B%3D12%2C%20E9%26gt%3B12))%2C%20E9%2C12).%20What%20is%20wrong%20with%20my%20logic%3F%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE9%20is%20always%20%22%26lt%3B%3D12%22%20(less%20than%20or%20equal%20to)%20or%20%22%26gt%3B12%22%20(greater%20than).%26nbsp%3B%20So%20the%20condition%20is%20always%20TRUE%2C%20and%20E9%20is%20always%20returned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20you%20intended%20to%20write%20IF(OR(E9%3CFONT%20size%3D%224%22%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3D%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FFONT%3E12%2CE9%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%26lt%3B%3C%2FFONT%3E%3C%2FSTRONG%3E12)%2C%20E9%2C%2012)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20that%20is%20the%20same%20as%20IF(E9%26lt%3B%3D12%2C%20E9%2C%2012)%2C%20which%20is%20better%20--%20but%20not%20better%20than%20the%20MIN%20expression%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2228880%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2228880%22%20slang%3D%22en-US%22%3EYour%20formula%20worked%2C%20thanks.%20Someone%20else%20sent%20me%20this%20formula%3A%20%3DMIN(12%2C%20A1).%20I%20don't%20understand%20it%20as%20well%20as%20yours%20but%20it%20seems%20simpler.%3C%2FLINGO-BODY%3E
Contributor

What I'm trying to do:

Cell A1 has a numeric value. In cell B1, I want to be able to do the following:

If A1 is =<12, enter the value in cell A1 into cell B1

If A1 is greater than 12, enter 12 into cell B1

My formula is:

=IF((OR(E9<=12, E9>12)), E9,12)

What is wrong with my logic?

 

4 Replies

Hi @marty007 

 

Please try below formula:

 

=IF(A1<=12,A1,12)

 

Thanks

Tauqeer

best response confirmed by marty007 (Contributor)
Solution

@marty007 

Re: ``If A1 is =<12, enter the value in cell A1 into cell B1. If A1 is greater than 12, enter 12 into cell B1``

 

=MIN(12, A1)

 

-----

 

Re: ``=IF((OR(E9<=12, E9>12)), E9,12). What is wrong with my logic?``

 

E9 is always "<=12" (less than or equal to) or ">12" (greater than).  So the condition is always TRUE, and E9 is always returned.

 

I suspect you intended to write IF(OR(E9=12,E9<12), E9, 12)

 

But that is the same as IF(E9<=12, E9, 12), which is better -- but not better than the MIN expression above.

Your formula worked, thanks. Someone else sent me this formula: =MIN(12, A1). I don't understand it as well as yours but it seems simpler.
IF(E9<=12, E9, 12) seems easier for me to grasp but =MIN(12, A1) is clearly simpler. I'm learning!
Thanks very much.