how to count the number of columns in each row of data that fall below a certain value

[#1]

If I have 4 columns in my table and I need to find out per row how many columns fall below 98% how can I write a simple formula to do this. 

There are some similar post but they are way more complicated than I need.

Thanks

[#2]

This is just a short version. The real table could have 100+ columns.

[#3 · Accepted solution]

Probably this formula could do:

test = Function( {val},
	If( val < 0.98, 1, 0 )
);
Sum( test( :A ), test( :B ), test( :C ), test( :D ) );

[#4]

Thanks Georg, that works!

[#5 · Accepted solution]

Hi @fionaweston ,

  To expand and generalize what @Georg posted, this script you can save to any data table and it will generate a new column with the counts. I wanted to add this because you mentioned that the data table could have hundreds of columns, and this can handle that, as well as however many rows you have.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Counts", Numeric, Continuous );

test = Function( {val},
	If( val < 0.98, 1, 0 )
);

For( r = 1, r <= N Rows(), r++,
	count = 0;
	ret = 0;
	For( col = 1, col <= N Col() - 1, col++, 
		If( Is Missing( Column( col )[r] ) == 1,
			count = 0,
			count = test( Column( col )[r] )
		);
		ret += count;
		:Counts[r] = ret;
	);
);

  You just click the hot button next to your data table name and select New Script, then copy/paste the script from above into the new script (and give it a name of course) and then click the green arrow to run it. You can then copy/paste that script into any data table -- of course, you would change the comparison value in the "test" function for whatever data set your using.

Hope this helps!,

DS

UPDATE: I noticed my original post didn't account for missing values in a column and corrected it to account for missing values.

[#6]

Thanks Diedrich,
This is awesome!

[#7]

Glad to hear it works for you!

[#8 · Accepted solution]

Hi @fionaweston ,

  As mentioned, here is the data table mock-up I did with the Counts script. It will only count numeric/continuous columns and ignore any other data/modeling type columns in your table.

Hope this helps!,

DS

[#9]

That helps enormously Diedrich.

Thankyou