Jul 25 2020 07:47 PM - edited Jul 25 2020 07:55 PM
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.
Jul 25 2020 08:10 PM
@suchetalahiri , wouldn't the result always be TRUE? Maybe I'm missing something. Do you have sample data (without any confidential info) to illustrate?
Jul 25 2020 08:16 PM
@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?
Jul 25 2020 08:25 PM
@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.
Jul 25 2020 08:37 PM
@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.
Jul 25 2020 08:46 PM
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.
Jul 25 2020 08:52 PM
@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?
Jul 25 2020 08:55 PM
Jul 25 2020 08:59 PM
@TheAntonyThank you and grateful for your help, George :)
Jul 25 2020 08:46 PM
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.