MIN with conditions and non-contiguous data

[Mike Collins]

Hi all, not 100% sure how to explain this. I have a spread sheet that lets me define when subjects are being taught and by who. Our time table is broken down into 6 blocks (A..F) and each block has 5 lessons (A1, A2, A3 etc). Each lesson is taught by a teacher who is allocated a room which has a capacity. I am trying to calculate the minimum capacity for each block.

Usually the first lesson of A-block is taught in A1 but in some cases it could be taught in B1 or any other position. I need to look through all blocks, identify those that contain a certain letter (in this case 'A') and then look up the room capacity, and then find the minimum value.

My spread sheet is too large and interwoven to upload but I have added in a png file detailing what I am trying to achieve.

Any help or advice would be amazing.

Many thanks

Mike C

 

[SirJB7]

Hi, Mike Collins!
Consider uploading a sample file instead of an image. It'd be easier to understand for people who might be able to help you.
Regards!

 

[r2c2]

@Mike Collins

Interesting question. I think a big part of your problem is the way data is structured. While the format works for entering timetable data, it is bloody tedious to figure out answers with that kind of setup. Ideally, I suggest setting up another sheet where your raw timetable data is rearranged in a tabular format for answering questions quickly.

At the very least if you can get two columns per class in the capacity area, then you could easily setup a formula to calc the min.

Barring that, you can use a plethora of array formulas (often very lengthy) to answer the question. Here is one. I am sure someone will better this.

Assuming,

• your class schedule is in G4:Z4 (two cells per class, total 20 for A&B, but could be more if you add more blocks)
• capacity is in cells AB4:AK4 (one cell per classroom, total 10 cells)
• and you have running numbers 0 to 9 in the range AB2:AK2 (not necessary, just makes the formula a bit shorter)
• classroom block starting letters in range A3:E3

This array formula gives the minimum capacity needed.

=MIN(IF(ISERROR(MATCH($AB$2:$AK$2, IF(LEFT($G4:$Z4,1)=A$3, (COLUMN($G4:$Z4)-COLUMN($G$4))/2),0)), FALSE,$AB4:$AK4))

What is going on here?

• We need the minimum of the range $AB4:$AK4 where for each cell, the corresponding classroom assignment starts with the same letter as in A$3.
• Start by finding which 5 cells out of 20 in the range g4:Z4 start with same letter as A3 - IF(LEFT($G4:$Z4,1)=A$3
• IF so, get the corresponding column number / 2 (ie convert 0 to 19 range to 0 to 9) - (COLUMN($G4:$Z4)-COLUMN($G$4))/2
• Match these column numbers with running numbers 0 to 9 in $AB$2:$AK$2 - MATCH($AB$2:$AK$2, IF(..COLUMN()),0)
• For each match that resulted in non-error, get corresponding value in $AB4:$AK4 - IF(ISERROR(MATCH(...)),FALSE,$AB4:$AK4)
• Get the minimum of these values - MIN(IF(...))

Hope that helps. Change the refs as per your data and let me know if anything is failing.

See attached workbook.