Home

Excel If Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-679134%22%20slang%3D%22en-US%22%3EExcel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679134%22%20slang%3D%22en-US%22%3EI%20created%20If%20function%20as%20shown%20in%20attached%20picture%20%3Dif%20(c1%26gt%3B%3D10%3B2%3B0)%20then%20if%20I%20type%20a%20letter%20or%20text%20into%20c1%20it%20show%20me%20the%20true%20value%3CBR%20%2F%3EWhy%20excel%20understand%20that%20letter%20or%20text%20as%20number%20%26gt%3B%3D10%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-679134%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eif%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679143%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357013%22%20target%3D%22_blank%22%3E%40Mfouad2255%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20with%20comparing%20operators%20any%20logical%20value%20is%20always%20more%20than%20any%20text%2C%20and%20any%20text%20is%20always%20more%20than%20any%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679145%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679145%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20reply%3CBR%20%2F%3EBut%20how%20I%20can%20fixed%20this%20I%20need%20to%20type%20text%20and%20the%20value%20not%20change%20or%20give%20me%20the%20value%20if%20false%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679146%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357013%22%20target%3D%22_blank%22%3E%40Mfouad2255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20didn't%20catch.%20You%20type%20some%20text%20in%20c1%20and%20compare%20it%20with%2010.%20When%20you'd%20like%20to%20have%20TRUE%20and%20when%20FALSE%2C%20for%20which%20combination%20of%20text%20and%2010%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679155%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679155%22%20slang%3D%22en-US%22%3EI%20want%20if%20I%20type%20the%20text%20in%20C1%20give%20me%20the%20value%20if%20false%20in%20D1%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679156%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679156%22%20slang%3D%22en-US%22%3EWhen%20I%20type%20text%20in%20C1%20it%20give%20me%20the%20value%20if%20false%20in%20D1%20not%20the%20value%20if%20true%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679165%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357013%22%20target%3D%22_blank%22%3E%40Mfouad2255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20to%20return%3C%2FP%3E%0A%3CP%3Eif%20in%20C1%20is%20text%20AND%20in%20D1%20is%20FALSE%20then%20value%3C%2FP%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CP%3EWhere%20to%20take%20value%3F%3C%2FP%3E%0A%3CP%3EIf%20in%20C1%20is%20text%20and%20in%20D1%20is%20TRUE%3F%3C%2FP%3E%0A%3CP%3EIf%20in%20C1%20is%20not%20text%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20staring%20point%20you%20may%20use%20check%20ISTEXT(C1)%20and%20build%20logic%20based%20on%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679166%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679166%22%20slang%3D%22en-US%22%3EKindly%20see%20this%20attached%20file%3CBR%20%2F%3EWhen%20I%20type%20the%20letter%20A%20or%20text%20in%20C1%20it%20give%20me%20the%20true%20value%202%3CBR%20%2F%3EAnd%20if%20I%20type%2010%20it%20give%20me%20same%20value%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679175%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357013%22%20target%3D%22_blank%22%3E%40Mfouad2255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISNUMBER(C1)*(C1%26gt%3B%3D10)%2C2%2C0)%3C%2FPRE%3E%0A%3CP%3Eif%20I%20understood%20your%20logic%20correctly.%20Please%20see%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679180%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679180%22%20slang%3D%22en-US%22%3EYes%20thanks%20for%20your%20cooperation%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679188%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357013%22%20target%3D%22_blank%22%3E%40Mfouad2255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Mfouad2255
Occasional Contributor
I created If function as shown in attached picture =if (c1>=10;2;0) then if I type a letter or text into c1 it show me the true value
Why excel understand that letter or text as number >=10
10 Replies

@Mfouad2255 ,

 

In Excel with comparing operators any logical value is always more than any text, and any text is always more than any number.

Highlighted
Thanks for your reply
But how I can fixed this I need to type text and the value not change or give me the value if false
Highlighted

@Mfouad2255 

 

Sorry, I didn't catch. You type some text in c1 and compare it with 10. When you'd like to have TRUE and when FALSE, for which combination of text and 10?

Highlighted
I want if I type the text in C1 give me the value if false in D1
Highlighted
When I type text in C1 it give me the value if false in D1 not the value if true
Highlighted

@Mfouad2255 

 

So, to return

if in C1 is text AND in D1 is FALSE then value

and

Where to take value?

If in C1 is text and in D1 is TRUE?

If in C1 is not text? 

 

As a staring point you may use check ISTEXT(C1) and build logic based on it

Highlighted
Kindly see this attached file
When I type the letter A or text in C1 it give me the true value 2
And if I type 10 it give me same value
Highlighted

@Mfouad2255 

 

Perhaps

=IF(ISNUMBER(C1)*(C1>=10),2,0)

if I understood your logic correctly. Please see in attached file.

Highlighted
Yes thanks for your cooperation
Highlighted

@Mfouad2255 

 

You are welcome, glad to help

Related Conversations
Insert an image in Excel header
mat74 in Excel on
1 Replies
DAX Powerpivot - How to solve this problem in PowerPivot
kayyeah11 in Excel on
0 Replies
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
4 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
1 Replies