Which function suits this scenerio best?

[Bpfrankl]

Hi I am confused as to which function would be best suited for the following situation. I have tried using vlookup, indirect, index, match and max with no success. I havent used excel since college in 2005. I have a feeling this is a fairly simple funtion but I have exhausted my brain digging through step by step books and watching you tube videos. I feel that if I had a good starting point with some tips that I could disect myself I would have a much better understanding of what is required and where.
My immediate goal is have row B3:B13 automatically display the coresponding row categories for the greatest values column. I can use the max function just fine to locate the greatest number.... I dont know how to properly forward the numeric value to read as the text.
Thanks,
Brad

[TABLE="width: 1007"]
<colgroup><col style="width: 60pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 132pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 6436;" width="176"> <col style="width: 153pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 7460;" width="204"> <col style="width: 128pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 6253;" width="171"> <col style="width: 95pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 4608;" width="126"> <col style="width: 80pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 3876;" width="106"> <col style="width: 68pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <col style="width: 72pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 3510;" width="96"> <col style="width: 76pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 3693;" width="101"> <col style="width: 143pt; margin-left: 40px; mso-width-source: userset; mso-width-alt: 6985;" width="191"> <tbody>[TR]
[TD="class: xl65, width: 80, bgcolor: fuchsia"]

​

[/TD]
[TD="class: xl70, width: 176, bgcolor: black"]

​

[/TD]
[TD="class: xl71, width: 204, bgcolor: transparent"]

Monday​

[/TD]
[TD="class: xl66, width: 171, bgcolor: transparent"]

Tuesday​

[/TD]
[TD="class: xl66, width: 126, bgcolor: transparent"]

Wednesday​

[/TD]
[TD="class: xl66, width: 106, bgcolor: transparent"]

Thursday​

[/TD]
[TD="class: xl66, width: 90, bgcolor: transparent"]

Friday​

[/TD]
[TD="class: xl66, width: 96, bgcolor: transparent"]

Saturday​

[/TD]
[TD="class: xl66, width: 101, bgcolor: transparent"]

Sunday​

[/TD]
[TD="class: xl67, width: 191, bgcolor: #FF6600"]

Week Totals​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

damaged blue seal on rack ​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

damaged worm​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

10​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

3​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

Dropped Gear or Component​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

11​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

High Pressure Smashed Poppet​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

5​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

9​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

6​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

25​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

5​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

incorrect assembly​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

6​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

misc unknown​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

6​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

7​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

missing sector plug​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

6​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

17​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

8​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

missing shipping components​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

9​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

preload set incorrectly​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

10​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

Stranded gear​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

2​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

7​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

9​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

11​

[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

Stripped Threads​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

1​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

5​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

4​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

0​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl68, bgcolor: #FF6600"]

Total Avoidable Rejects/Day:​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

11​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

17​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

23​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

16​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

5​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

19​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

21​

[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]

112​

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl69, bgcolor: #FF6600"]

Main Issue:​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

High Pressure Smashed Poppets​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

Dropped Gear or Component​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

Missing Sector Plug​

[/TD]
[TD="class: xl74, bgcolor: transparent"]

Stripped Threads​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

misc unknown​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

damaged worm​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

stripped threads​

[/TD]
[TD="class: xl73, bgcolor: transparent"]

High-pressure/Smashed Poppet​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bpfrankl]

My immediate goal is have row B3:B13 automatically display the coresponding row categories for the greatest values column.

I meant "range c13:j13" ,not "row B3:B13" oops..

 

[mikerickson]

A formula like =INDEX($B1:$B$12, MATCH(MAX(C1:C12), C1:C12, 0), 1) , dragged right, should do what you want.

 

[Peter_SSs]

Welcome to the MrExcel board!

What do you want to happen if 2 (or more) items have the equal maximum number?

Note that in that circumstance, Mike's solution would return the first one in the column.

(Also note that for the layout shown I think you would need to change all the "12"s in Mike's formula to "11"s as it appears row 12 is a total row.)

 

[Bpfrankl]

Hey thanks guys, your help is greatly appreciated. Thanks pete for the warm welcome. I have been neglecting my office programs for the past couple years. I am getting ready to go back to school so this is a good refreshment. :D

What do you want to happen if 2 (or more) items have the equal maximum number?

Well Pete that had crossed my mind as well... :/ Would it be possible to include a logical constraint that displays the multiple max names in that same cell separated with "\" or maybe "+". For Example: "Damaged Worm\Dropped Component"

My goal is to have a few charts that automatically display long and short term correlations that our quality engineers can use as tools to diagnose manufacturing issues.
Thanks,
Brad :D

 

[Bpfrankl]

Sorry to double dip again, but I did some research and brain storming and I think I am closing in on the solution... lol....
Feel free to show me how far off I am actually. :P
=INDEX(SUMIF(V4:V14,"=MAX(V4:V14)",[U4:U14]),(($U4:$U$14,MATCH(MAX(V4:V14),V4:V14,0),1)

SIDE NOTE: These are my actual row\column values. Just offset the scale from the example above: Columns:V=B, Row:4=1.

(Also note that for the layout shown I think you would need to change all the "12"s in Mike's formula to "11"s as it appears row 12 is a total row.)

Pete you are correct. :D

 

[Peter_SSs]

Would it be possible to include a logical constraint that displays the multiple max names in that same cell separated with "\" or maybe "+". For Example: "Damaged Worm\Dropped Component"

Brad

I think asking a formula solution in a single cell producing that would be extremely difficult. See if you can make any use of the formula suggestions below. If it really needs to be in a single cell, then you could look at concatenating the values in each column but you would have to allow for the fact that all 11 values might occur an equal number of times in a column. Alternatively, a macro solution may be better. Post back if you want to go down that path.

Formulas in V15 and V16 copied across.
Formula in V17 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.
The formula can then be copied across and down as far as you might ever need.

Excel Workbook
	U	V	W	X	Y	Z	AA	AB	AC
1		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Week Totals
2	damaged blue seal on rack	0	1	4	0	0	0	4	9
3	damaged worm	0	1	1	0	0	10	0	12
4	Dropped Gear or Component	1	2	2	0	1	1	0	7
5	High Pressure Smashed Poppet	5	9	4	0	1	0	0	19
6	incorrect assembly	1	1	1	0	0	0	0	3
7	misc unknown	0	0	4	0	2	0	6	12
8	missing sector plug	5	1	3	0	1	4	4	18
9	missing shipping components	1	1	1	0	0	0	0	3
10	preload set incorrectly	1	0	1	0	0	17	0	19
11	Stranded gear	0	0	2	0	0	0	7	9
12	Stripped Threads	1	1	1	0	0	4	0	7
13	Total Avoidable Rejects/Day:	15	17	24	0	5	36	21	118
14
15	Max Value	5	9	4	0	2	17	7	19
16	Max Count	2	1	3	11	1	1	1	2
17	Main Issue(s):	High Pressure Smashed Poppet	High Pressure Smashed Poppet	damaged blue seal on rack		misc unknown	preload set incorrectly	Stranded gear	High Pressure Smashed Poppet
18		missing sector plug		High Pressure Smashed Poppet					preload set incorrectly
19				misc unknown
20
Main Issues

 

[Bpfrankl]

Thanks Pete you are a life saver. :D I ended up using your suggested formulas and concatenating the cells. Thanks again!