A couple of array formula questions

[Sam Longstaff]

Hi,

I'm still struggling with compiling array formulas but I think that utilising them may help me with my current unsolved problems. This is for a work rota and I have attached an example and would like help with the following 2 related problems please.

1. I'd like a formula that will populate a small table showing who is working each day (based on rota codes - these are indicated in the example) by looking up a large rota table.
2. I'd then like another table populated showing how many times each staff member has worked with the other staff members by looking up the small table as described in point 1.

I hope someone knows how to solve these two issues. I've attached the file, many many thanks in advance.

Sam

 

[John Jairo V]

Hi, @Sam Longstaff!

Check this formulas in attach file:

1. [H3] : =IFERROR(INDEX($B$3:$B$33,AGGREGATE(15,6,ROW(C$3:C$33)-ROW(C$2)/MMULT(N({"WORK","WORK AM","WC FD","WC AM","WA CALL"}=C$3:C$33),{1;1;1;1;1}),ROWS(H$3:H3))),"")
drag it down and right.

2. [I15] : =IF($H15=I$14,"X",SUMPRODUCT(N(MMULT({1,1},COUNTIF(OFFSET($H$3:$H$12,,COLUMN($H$2:$K$2)-COLUMN($H$2)),CHOOSE({1;2},$H15,I$14)))=2)))
drag it down and right.

Blessings!

 

[Sam Longstaff]

WOW John!,

You've blown my mind with those, thank you very much. Is there any possibility you could explain a couple of the elements from the formulas that I don't quite understand:
From formula 1:
_xlfn.AGGREGATE

MMULT(N

{"WORK","WORK AM","WC FD","WC AM","WA CALL"}

{1;1;1;1;1}

From formula 2:
OFFSET

CHOOSE({1;2}

Also when ive gone into formula 1 in the green table to have a look at the formula and then returned out of it, it somehow does not apply. What have I done wrong?

Thanks for all your help.

 

[Sam Longstaff]

I think it may be that aggregate does not work on my version of Excel. I should have mentioned that I am only using windows 7.

Is there a solution available for my version?

 

[Marc L]

Hi !

As Excel formulas not depend on Windows version ! …​

 

[Sam Longstaff]

Its Excel 2007 too.

 

[John Jairo V]

Hi, Sam!

I'll try to explain. English is not my native language.

_xlfn.AGGREGATE

If appear the _xlfn preffix in a formula, is because the Excel version don't recognize the function.

Its Excel 2007 too.

In next opportunities, you must place the version of excel that you handle, because it is not so common that at this point still it continues using an old version of Excel. Just in case, it's not a criticism of your version, but a suggestion to keep in mind.

Now... replace the first formula for this (CSE formula --> must introduce with Ctrl + Shift + Enter and not just Enter):
[H3] : =IFERROR(INDEX($B$3:$B$33,SMALL(IF(MMULT(N({"WORK","WORK AM","WC FD","WC AM","WA CALL"}=C$3:C$33),{1;1;1;1;1}),ROW(C$3:C$33)-ROW(C$2)),ROWS(H$3:H3))),"")

For your understanding, you could see a link for mmult function:
https://exceljet.net/excel-functions/excel-mmult-function

this part:
{"WORK","WORK AM","WC FD","WC AM","WA CALL"}

Is a column matrix constant. (a matrix with 1 row and 5 columns --> 1 x 5)

and this one:
{1;1;1;1;1}

Is a row matrix constant. (a matrix with 5 rows and 1 column --> 5 x 1)

Now... this part:
N({"WORK","WORK AM","WC FD","WC AM","WA CALL"}=C$3:C$33)

Compares every element of matrix constant with C3:C33 range, and put 1 and 0's, depends on coincidence in range. The resultant matrix (in H3 for example) is like:
={1,0,0,0,0;0,0,0,0,1;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,1,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;1,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,1,0,0,0;0,0,0,0,0;0,1,0,0,0;0,0,0,0,0;0,0,0,0,0}

A matrix constant of 31 rows and 5 columns --> 31 x 5

In MMULT funcion, you have:
First matrix: 31 x 5
Second matrix: 5 x 1

Resultant matrix: 31 x 1
Like this:
{1;1;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0;1;1;0;0;0;0;0;1;0;1;0;0}

The 1's show us that there are a coincidence of any word that we list in the column matrix constant.

With the IF function, we put the relative row number in the matrix.
With SMALL function, we get the n-smallest number depends on the row of the formula, and pass to INDEX function, to get desired result.

Now, in second formula, the CHOOSE part:
=CHOOSE({1;2},$H15,I$14)

is a way to obtain a row matrix constant with the values in H15 and I14. The result of this part is (for H14 example):
{"S.Traynor";"S.Traynor"}

Blessings!