matrix - Excel: Count Occurences of Value in Rows of a Range -

in excel, need formula returns vector of boolean based on whether row contains specific value or not. example, range (n m matrix) contains random numbers 1 100. want vector of boolean based on whether number 41 has made appearance in row. if matrix named "matrix", result vector of length rows(matrix).

mmult(n(range=a1),transpose(column(range)))>0

where a1 contains search value, e.g. 41.

amend range required.

this may require committing cse, depending upon construction within employed.

as way of example, let's assume range here b1:f4, following values:

40 44 41 41 40

44 40 40 44 44

44 44 43 40 41

43 42 44 45 45

this part:

b1:f4=a1

simply returns array of boolean true/false entries whether each entry within range equal value in a1 or not, i.e.:

{false,false,true,true,false;false,false,false,false,false;false,false,false,false,true;false,false,false,false,false}

the n function coerces these booleans equivalent numerical values, i.e. true=1, false=0, such that:

n(b1:f4=a1)

gives:

{0,0,1,1,0;0,0,0,0,0;0,0,0,0,1;0,0,0,0,0}

we can see 1s occur in rows 1 , 3 (in case of row 1 more once). since 4-row-by-5-column array, taking matrix multiplication 5-row-by-1-column array (whose entries non-zero) produce 4-row-by-1-column array, entries in each row of being subject laws of matrix multiplication, such that:

mmult(n(b1:f4=a1),transpose(column(b1:f4)))

which is:

mmult({0,0,1,1,0;0,0,0,0,0;0,0,0,0,1;0,0,0,0,0},transpose(column(b1:f4)))

i.e.:

mmult({0,0,1,1,0;0,0,0,0,0;0,0,0,0,1;0,0,0,0,0},transpose({2,3,4,5,6}))

i.e.:

mmult({0,0,1,1,0;0,0,0,0,0;0,0,0,0,1;0,0,0,0,0},{2;3;4;5;6})

which is:

{9;0;6;0}

finally, check of these entries non-zero, such that:

mmult(n(b1:f4=a1),transpose(column(b1:f4)))>0

which is:

{9;0;6;0}>0

gives:

{true;false;true;false}

regards