Finding MAX of a text string based on numerically assigned values in a separate table.

[vlamish]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]office #[/TD]
[TD]address[/TD]
[TD]mon open[/TD]
[TD]mon close[/TD]
[TD]mon freq[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]123 fake[/TD]
[TD]9:00 AM[/TD]
[TD]8:00 PM[/TD]
[TD]monthly[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]456 fake[/TD]
[TD]7:00 AM[/TD]
[TD]5:00 PM[/TD]
[TD]bi-weekly[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]789 fake[/TD]
[TD]Closed[/TD]
[TD]Closed[/TD]
[TD]Closed[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]still fake[/TD]
[TD]9:00 AM[/TD]
[TD]6:00 PM[/TD]
[TD]weekly[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]monthly[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]bi-weekly[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]weekly[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[mrmmickle1]

Not sure I follow your logic?: Is this what your looking to do?

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).

Excel 2010
	A	B	C	D	E	F	G	H
1	office #	address	mon open	mon close	mon freq		Max Weekly Open	11:00 AM
2	12	123 fake	9:00 AM	8:00 PM	monthly		Max Weekly Close	11:30 PM
3	12	456 fake	7:00 AM	5:00 PM	bi-weekly		Min Weekly Open	2:00 AM
4	12	789 fake	Closed	Closed	Closed		Min Weekly Close	3:00 PM
5	12	still fake	9:00 AM	3:00 PM	weekly
6	12	456 fake	7:00 AM	8:00 PM	bi-weekly
7	12	789 fake	Closed	Closed	Closed
8	12	still fake	11:00 AM	11:00 PM	weekly
9	12	123 fake	9:00 AM	8:00 PM	monthly
10	12	456 fake	7:00 AM	5:00 PM	bi-weekly
11	12	789 fake	Closed	Closed	Closed
12	12	still fake	9:00 AM	11:30 PM	weekly
13	12	456 fake	7:00 AM	8:00 PM	bi-weekly
14	12	789 fake	Closed	Closed	Closed
15	12	still fake	2:00 AM	4:00 PM	weekly
Sheet1

Cell Formulas
Range	Formula
H1	=IFERROR(AGGREGATE(14,6,$C$2:$C$15/--($E$2:$E$15="weekly"),1),"")
H2	=IFERROR(AGGREGATE(14,6,$D$2:$D$15/--($E$2:$E$15="weekly"),1),"")
H3	=IFERROR(AGGREGATE(15,6,$C$2:$C$15/--($E$2:$E$15="weekly"),1),"")
H4	=IFERROR(AGGREGATE(15,6,$D$2:$D$15/--($E$2:$E$15="weekly"),1),"")

 

[vlamish]

Instead of trying to fail with more English, I created a few examples:

Imgur: The most awesome images on the Internet

The first screenshot is of my data table where I'm pulling all of the data. Sensitive information has been scrubbed and replaced by silliness.

The second screenshot contains my trouble. At the bottom of the screenshot, I provided the formulas I'm using (a minif and a maxif) to get the hours into this new calculations table above it. I have example data. As you can see, both the second and third examples have a close time of .833 hours (corresponds to 8pm). Thus my calculation in column E to calculate total hours reduces down to (8pm-6pm = 2 hour time difference ; 2 hours * weekly [4] = 8 qualifying hours).

My issue is this:
if data from group 1 in screenshot one has: their first location has monthly hours (a value of 1 within my j:k table), and their second location has weekly hours (a value of 4 in my j:k table), I want to choose weekly. I want "weekly" to show up in column D from the second screenshot so that my column E can be calculated correctly.

So, going back to screenshot 1:
I need to look up the group #, potentially with multiple instances within my table, and choose the maximum value in the "day freq" columns. I need a formula that will look at all entries in the column for any group # and choose the largest of them all.

I cannot modify the table from the first screenshot, as it is directly presented to others. The second screenshot, however, is just for me and I can do whatever to it.