Home

AND function

%3CLINGO-SUB%20id%3D%22lingo-sub-828648%22%20slang%3D%22en-US%22%3EAND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828648%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20issue%20when%20i%20use%20the%20AND%20function%20it%20gets%20all%20false%20values%20when%20i%20put%20this%20and%20of%20course%20i%20have%20values%20in%20between%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAND(K6%26gt%3B%3D70157%2CK6%26lt%3B70176)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20was%20just%20my%20first%20try%20on%20this%20function%20and%20went%20bad.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETrim%20%23%3C%2FTD%3E%3CTD%3EVALI%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70157%3C%2FTD%3E%3CTD%3E%3CSPAN%3EFALSE%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70158%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70162%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70160%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70163%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70164%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70166%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70165%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70167%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70170%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70169%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70175%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70176%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70186%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-828648%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnd%20function%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828681%22%20slang%3D%22en-US%22%3ERe%3A%20AND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400178%22%20target%3D%22_blank%22%3E%40SaintSebas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20your%20Trim%20%23%20column%20is%20formatted%20as%20text%20or%20you%20have%20text%20values%20in%20proper%20formatted%20column.%20Try%20%3DISTEXT(K6)%20in%20any%20empty%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828684%22%20slang%3D%22en-US%22%3ERe%3A%20AND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400178%22%20target%3D%22_blank%22%3E%40SaintSebas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20reason%20this%20isn't%20working%20is%20maybe%20because%20the%20Trim%23%20column%20is%20formatted%20as%20Text%20i.e.%20the%20numbers%20in%20there%20look%20like%20Numbers%20but%20actually%20Numbers%20as%20Text%20as%20pointed%20out%20in%20other%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20two%20options%20here%20to%20make%20your%20formula%20to%20work...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EYou%20convert%20your%20Numbers%20entered%20as%20Text%20as%20Real%20Numbers.%20To%20do%20so%2C%20select%20all%20the%20numbers%20in%20Trim%23%20column%2C%20go%20to%20Data%20Tab%20--%26gt%3B%20Text%20to%20Columns%20--%26gt%3B%20Click%20on%20Finish%20in%20the%20first%20step.%20And%20as%20a%20result%20your%20numbers%20will%20be%20converted%20as%20real%20numbers%20and%20your%20formula%20will%20start%20working.%3C%2FLI%3E%3CLI%3EIf%20you%20don't%20want%20to%20change%20anything%20with%20your%20data%2C%20you%20may%20tweak%20your%20formula%20to%20%3CSTRONG%3E%3DAND(K6%2B0%26gt%3B%3D70157%2CK6%2B0%26lt%3B70176)%3C%2FSTRONG%3E.%20Adding%200%20to%20K6%20(K6%2B0)%20or%20multiplying%20K6%20with%201%20(K6*1)%20will%20change%20the%20number%20in%20K6%20to%20a%20real%20number.%20In%20other%20words%2C%20if%20a%20number%20is%20entered%20as%20a%20Text%2C%20you%20can%20perform%20a%20mathematical%20operation%20on%20that%20number%20and%20in%20this%20case%20adding%20a%20zero%20or%20multiplying%20the%20number%20by%201%20doesn't%20change%20it's%20value%20but%20then%20you%20can%20compare%20it%20with%20another%20real%20number%20as%20you%20are%20doing%20in%20your%20formula.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E
SaintSebas
Occasional Visitor

I have this issue when i use the AND function it gets all false values when i put this and of course i have values in between

 

=AND(K6>=70157,K6<70176)

 

This was just my first try on this function and went bad.

 

Trim #VALI
70157FALSE
70158FALSE
70162FALSE
70160FALSE
70163FALSE
70164FALSE
70166FALSE
70165FALSE
70167FALSE
70170FALSE
70169FALSE
70175FALSE
70176FALSE
70186FALSE
2 Replies

@SaintSebas 

Most probably your Trim # column is formatted as text or you have text values in proper formatted column. Try =ISTEXT(K6) in any empty cell.

@SaintSebas 

 

The only reason this isn't working is maybe because the Trim# column is formatted as Text i.e. the numbers in there look like Numbers but actually Numbers as Text as pointed out in other reply.

 

You have two options here to make your formula to work...

 

  1. You convert your Numbers entered as Text as Real Numbers. To do so, select all the numbers in Trim# column, go to Data Tab --> Text to Columns --> Click on Finish in the first step. And as a result your numbers will be converted as real numbers and your formula will start working.
  2. If you don't want to change anything with your data, you may tweak your formula to =AND(K6+0>=70157,K6+0<70176). Adding 0 to K6 (K6+0) or multiplying K6 with 1 (K6*1) will change the number in K6 to a real number. In other words, if a number is entered as a Text, you can perform a mathematical operation on that number and in this case adding a zero or multiplying the number by 1 doesn't change it's value but then you can compare it with another real number as you are doing in your formula.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies