Want to check each numeric column for each row of test and then assign a value (yes or No)

Question

Hi,

I want to go through each row in this table for compare value 1 thru value 8, and if one value is less than 1 and any other remaining value is greater than 1, then i say yes or no in the result column. How do i do that?

IDvalue 1value 2value 3value 4value 5value 6value 7value 8Result 32001105030.0640640000000Yes 32001105200.0578640000000No 32001105460.069850000000 320011056200.064931000000 32001105970.062410000000

txnelson · Answer

Here is a hardcoded straight forward piece of formula that will create the results you want, given your latest description If( (:value 1 > 0 & :value 1 < 1 | :value 2 > 0 & :value 2 < 1 | :value 3 > 0 & :value 3 < 1 | :value 4 > 0 & :value 4 < 1 | :value 5 > 0 & :value 5 < 1 | :value 6 > 0 & :value 6 < 1 | :value 7 > 0 & :value 7 < 1 | :value 8 > 0 & :value 8 < 1) & Max( :value 1, :value 2, :value 3, :value 4, :value 5, :value 6, :value 7, :value 8 ) > 1, "Yes", "No" )

samsam · Answer

D value 1 value 2 value 3 value 4 value 5 value 6 value 7 value 8 Result 
3200110503 0.064064 0 0 0 0 0 1.5 0 Yes 
3200110520 0.057864 0 0 0 0 0 0 0 No 
3200110546 0.06985 0 0 0 0 0 0 2.6 Yes 
3200110562 0 0.064931 0 0 0 0 0 0
3200110597 0.06241 0 0 0 0 0 0 0

I am referring to check each column for each row to look for two conditions:
1. Check each column to identify any column has value greater than 0 and less than1.
2. Check each column again to identify if any column has value greater than 1.
3. If statement 1 and statement 2 is true for any row, then i report value Yes. If false, then i report value No.

Thanks again for helping.

samsam · Answer

Hi,

My table is long and there are some columns for each row that has value greater than 0 and less than 1. Then next column for the same row is greater than 1.

So I want my logic as follow:

1. Check each column to see the values are greater than 0 and less than 1.

2. Second, check each column again to see if the values are greater than 1.

3. Third, if there are two columns, one represent between 0 and 1 and any other column that represents greater than 1, I fill in the result column as true.

Please let me know if that makes sense.