Having used the MrExcel forums for plenty of excel help over the past months, I have finally found something that google searches don't seem to provide answers for. So, here goes.
I have a set of data that is based off of a business number. Multiple instances of the same business number can appear in the table if the business has multiple locations. I am trying to calculate hours of operation outside of a specific window based on the hours given and the frequency of those hours (once a month, bi-weekly, weekly, etc). I have a separate table for these calculations and I've already used MAXIF and MINIF to find the best open/close hours. I had used a complicated set of if, index, and vlookup functions to find appropriate data based on whether an office becomes closed. However, the limit of this complicated mess has been reached.
I already have assigned numeric values to the 5 options in my frequency columns: "" = 0, "Closed" = 0, "monthly" = 1, "bi-weekly" = 2, "weekly" = 4. What I want to do is have a max function that looks up all instances of a business number, finds the maximum value of the frequency (based on the lookup table I created) and then prints the specific text into my calculation table.
example:
<tbody>
</tbody>
<tbody>
</tbody>Note: the blank cell is indeed blank, or "".
I can correctly get 7:00 AM as the earliest open, I can correctly find 8:00 PM as the latest close. I cannot correctly select "weekly" as the MAX text option based on its assigned value of 4. I know this will need to be an array formula, but I simply cannot construct a formula based on the logic in my head. Any help would be greatly appreciated.
p.s. Yes, I realize that the 7:00 AM open time should theoretically be paired with a bi-weekly value of 2, but that simply isn't how we calculate things. We take earliest open, earliest close, and best frequency to calculate hours.
I have a set of data that is based off of a business number. Multiple instances of the same business number can appear in the table if the business has multiple locations. I am trying to calculate hours of operation outside of a specific window based on the hours given and the frequency of those hours (once a month, bi-weekly, weekly, etc). I have a separate table for these calculations and I've already used MAXIF and MINIF to find the best open/close hours. I had used a complicated set of if, index, and vlookup functions to find appropriate data based on whether an office becomes closed. However, the limit of this complicated mess has been reached.
I already have assigned numeric values to the 5 options in my frequency columns: "" = 0, "Closed" = 0, "monthly" = 1, "bi-weekly" = 2, "weekly" = 4. What I want to do is have a max function that looks up all instances of a business number, finds the maximum value of the frequency (based on the lookup table I created) and then prints the specific text into my calculation table.
example:
office # | address | mon open | mon close | mon freq |
12 | 123 fake | 9:00 AM | 8:00 PM | monthly |
12 | 456 fake | 7:00 AM | 5:00 PM | bi-weekly |
12 | 789 fake | Closed | Closed | Closed |
12 | still fake | 9:00 AM | 6:00 PM | weekly |
<tbody>
</tbody>
0 | |
Closed | 0 |
monthly | 1 |
bi-weekly | 2 |
weekly | 4 |
<tbody>
</tbody>
I can correctly get 7:00 AM as the earliest open, I can correctly find 8:00 PM as the latest close. I cannot correctly select "weekly" as the MAX text option based on its assigned value of 4. I know this will need to be an array formula, but I simply cannot construct a formula based on the logic in my head. Any help would be greatly appreciated.
p.s. Yes, I realize that the 7:00 AM open time should theoretically be paired with a bi-weekly value of 2, but that simply isn't how we calculate things. We take earliest open, earliest close, and best frequency to calculate hours.