Building a Chart that only shows top 3 losses.

[TSing3]

I currently have a data table that shows hours lost by factory line.

Column A contains all the downtime reasons (There are 15). Rows B2:W2 have all of the Line numbers. Finally B5:W21 have all of the data that shows all the losses by line for each downtime reason.

I want to build 1 chart where anyone can go in and select the line number and the chart will show their top 3 losses by reason. I tried using the LARGE function to pull out the 3 largest losses per line, but that only pulls the data, it does not pull the reason associated with that data.

I'm kind of at a loss.

 

[istiasztalos]

Hi TSing3,

You can use the INDEX function to get the reason name from the same position in a column as something else in another column. The position of the item you looked up, here the Nth largest loss can be found with the formula MATCH. This INDEX/MATCH combination is often used in Excel instead of VLOOKUP because it gives you great flexibility, the two columns can be placed anywhere in your spreadsheet as long as they are the same size.

In your case, let's say you have factory-line5 in column F, data in F5:F19, to find the top 1-2-3 reason codes this would be your formula:
=INDEX(A5:A19,MATCH(LARGE(F5:F19,1),F5:F19,0))
=INDEX(A5:A19,MATCH(LARGE(F5:F19,2),F5:F19,0))
=INDEX(A5:A19,MATCH(LARGE(F5:F19,3),F5:F19,0))

If you want a dropdown selector for your users to select their factory line you could
- either use these formulas in just one place and define the reference to the range of data using INDIRECT (you'd have to have users select column letter or have named ranges setup for each factory line)
- or have these formulas at the bottom of each factory line's column and use other lookups (HLOOKUP or another INDEX/MATCH combination) to find the results for the particular factory line selected by the user