Excel, conditional function to calculate next value within a list based on boolean
Sorry if the title isn't clear enough. I didn't find a nice way to phrase what I'm trying to achieve here. I'm an excel newbie so don't know where to start with the logic for what I'm looking for.
I have a spreadsheet I want to use to schedule cleaning in a student corridor. Thing is that rooms are not always occupied, so it needs to be able to react whether the next room is occupied or not and if not, select next one on the list.
In this image we can see how week 22 was cleaned by 1902 and 1903, next week turn should be next two rooms in [1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911] as long as they are booked (value 1 in the columns of the right). 1904 and 1905 are booked (green) so next week will be their turn.
If we continue with the same logic next turn will be 1906&1907 and next week (weeknumber 25) should be 1908&1909, problem is that room 1908 is empty right now so it shouldn't be counted as an eligible room and instead choose 1909&1910 as next cleaning rooms.
How could I achieve this logic with an excel spreadsheet?
Here is a link for a copy of my spreadsheet so you can see what I'm trying to achieve and play with original data if needed.
Short answer
I'm fairly sure it's this:
=IF(NOT(ISNA(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1)), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1) & "-" & IF(NOT(ISNA(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1)))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))+1-2)),0))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0)) & "-" & IF(NOT(ISNA(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1)))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))+1-2)),0))))
Paste this in below a row which already has a turn in it, e.g. D6 (the turn for week 27). This formula uses the previous turn and the range of room names ($G$1:$Q$1) to determine what the next turn should be.
Long answer
Wait, what is this monstrosity!? It's the most daunting formula I've ever seen too!
Well, there's a lot of repetition in there so that it works independently. It's also doing a lot of heavy lifting so that it's e.g. flexible to how many rooms there might be, what the present row is and to any rooms being booked. There could be even more logic to handle edge cases such as, what if all rooms are booked? (currently it returns #N/A), but this solves the fundamental problem.
Breaking it down into steps:
Find which room was cleaned last
Using cell D6 as a starting point, the last room to be cleaned is 1910. So, we want to find this value in cell D5:
=INT(RIGHT(D5, LEN(D5)-FIND("-", D5)))
This simply takes the text from D5 and extracts the last number.
Locate that within the range
The range $G$1:$Q$1 is the list of cells on the first row that specify the room numbers ($ means that this is fixed, so if you drag this formula down, for example, the range will still refer to G1:Q1 and not anything below it).
The 1st column of this range (G) is the 7th column in the spreadsheet. This (7) can be found with the following formula:
=COLUMN($G$1:$Q$1)
The number of columns in the range (11) is:
=COLUMNS($G$1:$Q$1)
To find the last cleaned room in the range, we use MATCH with Match_type = 0:
MATCH finds the first value that is exactly equal to lookup_value.
=MATCH( [Last room number] ,$G$1:$Q$1,0)
=MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0)
This returns the relative column number, from 1 to 11, of the the matching cell (1910 is on the 10th column).
Identify target range
We now want to start looking at the next column after that. We could simply add 1, which works fine unless the last room to be cleaned was 1911. In that case, we can't just add 1, because there's no 12th room. There, we instead want to go back round to the 1st room. To do this, we first use MOD and divide by the number of columns:
=MOD( [Relative column number] ,$G$1:$Q$1,0), [Column count] ) +1
=MOD( MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1
The number returned by this is the column to start with. So, if the last room was 1910, then we get 11 as a result. The column to end with is the last one, 11 (the column count).
However, if no unbooked rooms are found in this range, then we have to go around and check again from the first to the one before the start. This means we always need to do two checks to maintain the sequence of rooms.
In our example:
| Check | Start | End | Range |
|---|---|---|---|
| 1 | 11 | 11 | Q6:Q6 |
| 2 | 1 | 10 | G6:P6 |
As pseudo-formulae:
| Check | Start | End | Range |
|---|---|---|---|
| 1 | MOD( [Relative column number] ,$G$1:$Q$1,0), [Column count] ) +1 | [Column count] | =INDIRECT(ADDRESS(ROW(), [Column count before first] + [Start] )):INDIRECT(ADDRESS(ROW(), [Column count before first] + [End] )) |
| 2 | 1 | [Last room column] | =INDIRECT(ADDRESS(ROW(), [Column count before first] + [Start] )):INDIRECT(ADDRESS(ROW(), [Column count before first] + [End] )) |
As formulae:
| Check | Start | End | Range |
|---|---|---|---|
| 1 | =MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1 |
=COLUMNS($G$1:$Q$1) |
=INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1 )):INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1) )) |
| 2 | 1 |
=MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) |
=INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1) )):INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) )) |
Some formula acrobatics needs to be done to describe this as a range to Excel. These ranges are specifying the present row, and giving the absolute column reference (remember, the 1st column of the range is the 7th column of the spreadsheet, so we need to add 6, or =COLUMN($G$1:$Q$1) -1 to these)
Find next unbooked room
Now we can use that with MATCH to find the next unbooked room in each case:
=MATCH(1, [Range] ,0) + [Start] -1
=MATCH(1, INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1 )):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1) )) ,0)+ MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) )
(Returns 11)
=MATCH(1, [Range] ,0)
=MATCH(1, INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1) )):INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) )) ,0)
(This is slightly simpler as start is always 1)
(Returns 1)
This means that a value of 1 has been found on the 11th and 1st columns.
Finding room numbers
To find the actual room numbers from these column numbers, we use INDEX:
=INDEX($G$1:$Q$1,1, [Matching column number]) Returns 1901 and 1911 for the above.
Conditional logic
If the first check found a number (returning #N/A otherwise), we don't need the second. In other words, if the next unbooked room was found after the last one, then we don't need to go round to room 1901 to continue checking. In Excel, this is an IF function:
=IF(NOT(ISNA( [Check 1] )), INDEX($G$1:$Q$1,1, [Check 1]), INDEX($G$1:$Q$1,1, [Check 2]))
However, we are searching for two rooms, which may be in the first and second check, or both be within the first check, or both within the second, or otherwise not found. As MATCH only finds the first match in the range, we need to do this once, adjust the range based on the result, then do this all again. The overall formula becomes a combination of IF functions, like this:
=IF(NOT(ISNA( [Check 1] )),
INDEX($G$1:$Q$1,1, [Check 1]) & "-" & IF(NOT(ISNA( [Check 3] )), INDEX($G$1:$Q$1,1, [Check 3]), INDEX($G$1:$Q$1,1, [Check 4])),
INDEX($G$1:$Q$1,1, [Check 2]) & "-" & IF(NOT(ISNA( [Check 3] )), INDEX($G$1:$Q$1,1, [Check 3]), INDEX($G$1:$Q$1,1, [Check 4]))
)
When you combine all of that, the result is the big formula from the beginning.
Alternative answer
This actually looks much simpler in VBA code. Here is a guide on how to set this up in Excel. Use the function below:
Public Function choose_turns(last As Range, rooms As Range) As String
Dim row As Integer
Dim rooms_first_column As Integer
Dim rooms_count As Integer
Dim last_room_number As Integer
Dim last_room_column As Integer
Dim count_rooms_found As Integer
Dim start_column As Integer
Dim end_column As Integer
Dim found_column As Integer
Dim target As Range
Dim result As String
'Find which room was cleaned last
last_room_number = Int(Mid(last.Value, InStr(last.Value, "-") + 1))
'Locate that within the range
rooms_first_column = rooms.Column
rooms_count = rooms.Columns.Count
last_room_column = Application.Match(last_room_number, rooms, 0)
'Conditional logic
row = last.row + 1
count_rooms_found = 0
start_column = last_room_column
Do While count_rooms_found < 2
found_column = 0
'Check 1
'Identify target range
start_column = (start_column Mod rooms_count) + 1
end_column = rooms_count
Set target = Range(Cells(row, rooms_first_column - 1 + start_column), Cells(row, rooms_first_column - 1 + end_column))
'Find next unbooked room
If Not IsError(Application.Match(1, target, 0)) Then
found_column = Application.Match(1, target, 0) + start_column - 1
Else
'Check 2
'Identify target range
start_column = 1
end_column = last_room_column
Set target = Range(Cells(row, rooms_first_column - 1 + start_column), Cells(row, rooms_first_column - 1 + end_column))
'Find next unbooked room
If Not IsError(Application.Match(1, target, 0)) Then
found_column = Application.Match(1, target, 0) + start_column - 1
End If
End If
If found_column = 0 Then
Exit Do
End If
If 0 < Len(result) Then
result = result + "-"
End If
'Finding room numbers
result = result + CStr(Application.WorksheetFunction.Index(rooms, 1, found_column))
count_rooms_found = count_rooms_found + 1
Loop
choose_turns = result
End Function
Then, in your worksheet, you can use it as a formula like this (e.g. in D6):
=choose_turns(D5, $G$1:$Q$1)
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port