Staff Rota Array: Evaluate Multiple Columns

**JoeByrne** · 04-25-2012, 06:02 AM

Hi All,

I've got a staff rota table that has half hour increments as column headers. The first row header is whether the store is trading that time slot and then staff positions listed down beneath that. Each cell in the matrix has a 1 if the staff member is working that time slot (and 0 if they are not).

In the totals column down the side, I have total hours worked (sum the 1's and divide by 2 to get hours). I'm trying to work out an array formula to sit next to that which will add up the hours that each staff member is on their own in the shop with customers (logic being that per half hour slot, the Store Trading row = 1, that Staff Member column = 1 AND all other cells in that column = 0). Obviously very easy to compare a single column - slightly harder when there are 60!

Have played with the FREQUENCY formula to look at the columns that match the criteria but couldn't get it working. Basically what I think I need to do is to add each level of the array together (to be left with column totals) and then those which = 0 are what I need. Just can't for the life of me work out how to do that!!

Any pointers in the right direction would be much appreciated.

Cheers

J

**benishiryo** · 04-25-2012, 10:08 AM

hi JoeByrne,

an attachment would help us understand better
=)

**JoeByrne** · 04-25-2012, 10:34 AM

Example.xls

A good point! Not sure if I've got the upload system sorted but hopefully this works - see attached. Thanks for helping benishiryo...

**FDibbins** · 04-25-2012, 11:11 AM

at the botton of your data in Column C, add this formula, and copy across...

=IF(AND(C3=1,SUM(C4:C14)>1),1,"")

you can then add across in BM15 to give you the total

let me know how this works for you?

**JoeByrne** · 04-25-2012, 01:01 PM

Thanks FDibbins,

That does calculate when more than 1 staff member is working. Unfortunately, I'm trying to calculate for each staff member, how many hours they are lone trading. I could do it by customising the formula for each row - just wanted to know if it's possible to use an array so I can use a generic formula.

Actually, you gave me an idea which has worked. I added a normal total row at the bottom of the data table and then used the following formula (from Cell BM4) which works well.

=SUM(($C$3:$BJ$3>0)*(C4:BJ4>0)*($C$15:$BJ$15=1)*(C4:BJ4))/4

Would still be interested to know how it could be done without the total column for learning sake, but otherwise would say this is solved. Thanks again!!