Creating a value to be returned based on the maximum value

[zilch4ry]

hi all,

right this is a hard question - i have 4 values which are being found using a vlookup- the vlookup is gathering data from a table which is based off of which day of the week it is. the 4 pieces of data have been set into 4, 4 hour gap groups (between 6-10 am, 10-2pm,2-6pm,6-10pm). is it possible to find the busiest hour using something like, =max(g10,g12,l10,12) but instead of showing the maximum number, it shows the hour the value is from. so something like =if (this value is the maximum number, then show this hour, if it isnt show nothing) and so on with all the values.

if that made sense to anybody you are a smart person but seriously any help would be GREAT!

Thanks everyone,


Tom

 

[snoopyhr]

Looks a bit simple, hope not missed anything, here is my 2 cents on this :
Assuming the table of lookup already setup and working, that is the table shown if the lookup result.

Excel Workbook
	A	B	C	D	E	F
2	Day of the week	6-10 am	10-2 pm	2-6 pm	6-10 pm	Time @ Max
3	Monday	139	140	129	134	10-2 pm
4	Tuesday	105	137	126	122	10-2 pm
5	Wednesday	148	145	127	168	6-10 pm
6	Thursday	148	107	129	171	6-10 pm
7	Friday	155	188	179	175	10-2 pm
8	Saturday	196	171	171	196	6-10 am
9	Sunday	189	186	158	186	6-10 am
10	Monday	136	173	185	188	6-10 pm
11	Tuesday	188	179	190	151	2-6 pm
12	Wednesday	118	151	175	182	6-10 pm
13	Thursday	142	159	129	141	10-2 pm
Sheet2

 

[Aladin Akyurek]

How about providing a small sample that shows how your data is represented?

 

[zilch4ry]

Hi,

heres the screen shot of my tables, i think it might be a little hard to do something based off of them, considering i have placed the titles in shapes. I was hoping more for a formula answer.

screen shot:

What i want is the highest value (in green) of the day (in each table) to be shown and if there are two highest values for them to be seperated using something like a comma :P for the life of me i cannot even get close to doing this

 

[zilch4ry]

Is this even possible? even if you know the formulas that might be helpful

 

[snoopyhr]

see if this works for you,

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Table named range : R_6_10						Table named range : R_10_14
2	06:00_10:00	Average No. of items purchased per customer visit	Average No. customers per hour	Average No. of Customers 1 staff member can get through per hr	No. of tills needed per hour open-w/4 customers in a que		10:00_14:00	Average No. of items purchased per customer visit	Average No. customers per hour	Average No. of Customers 1 staff member can get through per hr	No. of tills needed per hour open-w/4 customers in a que
3	Monday	180	147	100	10		Monday	20	180	26	1
4	Tuesday	60	300	26	2		Tuesday	150	160	30	30
5	Wednesday	60	0	26	2		Wednesday	70	10	26	2
6	Thursday	190	200	26	2		Thursday	10	200	26	2
7	Friday	60	280	26	3		Friday	70	280	26	3
8	Saturday	60	306	60	60		Saturday	70	280	26	3
9	Sunday	60	110	26	1		Sunday	70	107	80	70
10
11	Table named range : R_18_22						Table named range : R_18_22
12	14:00_18:00	Average No. of items purchased per customer visit	Average No. customers per hour	Average No. of Customers 1 staff member can get through per hr	No. of tills needed per hour open-w/4 customers in a que		18:00_22:00	Average No. of items purchased per customer visit	Average No. customers per hour	Average No. of Customers 1 staff member can get through per hr	No. of tills needed per hour open-w/4 customers in a que
13	Monday	200	147	26	1		Monday	90	147	26	1
14	Tuesday	80	160	26	2		Tuesday	90	160	26	2
15	Wednesday	80	0	40	40		Wednesday	30	160	26	2
16	Thursday	80	300	26	2		Thursday	30	200	50	50
17	Friday	120	280	35	10		Friday	300	302	26	3
18	Saturday	1000	280	26	3		Saturday	90	280	26	3
19	Sunday	80	107	26	80		Sunday	90	107	26	1
20
21
23
24
25		06:00_10:00	10:00_14:00	14:00_18:00	18:00_22:00
26	Monday	100
27	Tuesday		30
28	Wednesday			40
29	Thursday				50
30	Friday			35
31	Saturday	60
32	Sunday		80
Sheet1 (2)

 

[zilch4ry]

wow! thank you very much