SOLVED

Help me with if then function!

%3CLINGO-SUB%20id%3D%22lingo-sub-1546107%22%20slang%3D%22en-US%22%3EHelp%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546107%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%20members%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20be%20really%20grateful%20if%20you%20could%20help%20me%20with%20this%20question%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20seven%20columns%20that%20are%20either%20filled%20or%20not%20filled.%20I%20need%20a%20function%20to%20fulfil%20the%20following%20conditions%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EFirst%20condition%3A%20the%20first%20column%20can%20be%20filled%20with%20numbers%20with%20a%20number%3C%2FP%3E%3CP%3Eor%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%20condition%3A%20the%20first%20column%20can%20be%20blank%3C%2FP%3E%3CP%3EIf%20both%20the%20conditions%20fulfil%2C%20then%20it%20should%20populate%20%221%22%2C%20else%20%220%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1546107%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-1546127%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%2C%20wouldn't%20the%20result%20always%20be%20TRUE%3F%20Maybe%20I'm%20missing%20something.%20Do%20you%20have%20sample%20data%20(without%20any%20confidential%20info)%20to%20illustrate%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546133%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3EHi%20Anthony%2C%20I%20cant%20share%20the%20data%20as%20I%20am%20doing%20a%20research%20project%20on%20it.%20I%20don't%20want%20true%20or%20false%2C%20I%20want%20it%200%20or%201%20as%20the%20data%20is%20to%20be%20used%20for%20further%20analysis.%20Guess%20even%20if%20we%20can%20do%20true%20or%20false%2C%20we%20can%20turn%20it%20into%200%20or%201%20with%20the%20further%20formula%2C%20no%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546148%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%2C%20yes%20the%20result%20will%20be%200%20and%201.%20This%20is%20the%20process%20of%20constructing%20the%20conditional%20formula%20you%20are%20requesting.%20My%20question%20is%20around%20the%20logic.%20Based%20on%20what%20I%20understand%20from%20your%20explanation%2C%20the%20result%20will%20always%20be%201.%20Perhaps%20you%20can%20let%20me%20know%20the%20error%20in%20my%20logic%20by%20looking%20at%20the%20sample%20file%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546154%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3EHi%20George%2C%20I%20have%20uploaded%20a%20sample%20file%20just%20to%20explain%20the%20background%2C%20please%20let%20me%20know%20if%20you%20can%20crack%20it.%20Thank%20you%2C%20kindly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546160%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%2C%20ok%2C%20I%20think%20I%20got%20it.%20If%20there%20are%20more%20than%201%20values%20in%20the%20categories%2C%20then%20result%20should%20be%200.%20No%20value%20or%201%20value%20results%20in%201.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546161%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3EYou're%20a%20genius!%20yes%2C%20it%20did!%20My%20next%20question%20is%20what%20if%20we%20have%20following%20condition%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20should%20get%201%20if%20the%20count%20is%20at%20least%20two%20categories%20or%20more%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546162%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%20guess%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(COUNTA(A16%3AK16)%26gt%3B%3D2%2C1%2C0)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Esorry%20just%20copied%20yours%20and%20tried%20my%20logic!%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546163%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%2C%20you%20got%20it.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546165%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3EThank%20you%20and%20grateful%20for%20your%20help%2C%20George%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546168%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20with%20if%20then%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F728699%22%20target%3D%22_blank%22%3E%40suchetalahiri%3C%2FA%3E%26nbsp%3B%2C%20Glad%20I%20could%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

hello members, 

 

I will be really grateful if you could help me with this question:

 

I have seven columns that are either filled or not filled. I need a function to fulfil the following conditions:

Either one of the columns has a number value

or

no number value in any of the columns

 

If either of the above conditions fulfil, I should have "1" as a column value, else 0. 

 

10 Replies

@suchetalahiri , wouldn't the result always be TRUE? Maybe I'm missing something. Do you have sample data (without any confidential info) to illustrate? 

@TheAntonyHi Anthony, I cant share the data as I am doing a research project on it. I don't want true or false, I want it 0 or 1 as the data is to be used for further analysis. Guess even if we can do true or false, we can turn it into 0 or 1 with the further formula, no?

@suchetalahiri , yes the result will be 0 and 1. This is the process of constructing the conditional formula you are requesting. My question is around the logic. Based on what I understand from your explanation, the result will always be 1. Perhaps you can let me know the error in my logic by looking at the sample file attached.

@TheAntonyHi George, I have uploaded a sample file just to explain the background, please let me know if you can crack it. Thank you, kindly.

Best Response confirmed by suchetalahiri (Occasional Contributor)
Solution

@suchetalahiri , ok, I think I got it. If there are more than 1 values in the categories, then result should be 0. No value or 1 value results in 1. See attached.

@TheAntonyYou're a genius! yes, it did! My next question is what if we have following condition:

 

We should get 1 if the count is at least two categories or more?

@TheAntony  guess this?

 

=IF(COUNTA(A16:K16)>=2,1,0)

 

sorry just copied yours and tried my logic!

@suchetalahiri , you got it.

@TheAntonyThank you and grateful for your help, George

@suchetalahiri , Glad I could help!