Hi all,



My knowledge of Excel is pretty basic, and the things I am trying to do are above my level of knowledge thus far. Im working with Excel 2007 on a Macbook. Let me explain in detail~

On Sheet1 I have a table with columns from A to O. Each of them have filters engaged. If I toggle through those filters in columns A, B, and C, I can break it down to the level I want and I get the desired range of information displayed in Column O.

What I want is to export the consolidated range of information now found in Column O to Sheet3 and display the top three numerical values from Column O.

To specify with an example, Column A=Employee, Column B=Operation, Column C=Size. Column O would be the time it takes the employee to conduct the number of products in one hour on average. So when I filter out Columns A, B, and C, I can gauge this subset of information: Employee=Cody, Operation=Bending, Size=Small Rod. Evidently We can see in Column O that Cody bends 100 small rods per hour on average.

These performances are recorded over time and are updated in real time. Therefore, Whatever Fx is to be used to get the top three performances displayed on Sheet3 would have to be able to accomodate for these additions to Sheet1.

Im thinking that I need an IF statement to show that the array is contingent on what text is displayed in each column (specifying "CODY","BENDING","SMALL_ROD"). Since there are muliple congingencies for the range, they would probably have to incorporate an AND statement. I was thinking that the positive outcome of the IF statement could be a large statement, specifiying 1,2,or 3 for the kth value. It doesnt matter what the negative outcome of the IF statement is, so I've just been putting "FIX_IT". My Fx looks something like this right now: =IF(AND(SHEET1!A:A="CODY",SHEET1!B:B="BENDING",SHE ET1!C:C="SMALL"),LARGE(SHEET1!0:0,1),"FIX_IT"). The cell is coming up with FIX_IT, what am I doing wrong?

It would be awesome for someone to help me write a Fx to solve my problem. Ive been looking at what other people have encountered and I cant find exactly what I need. Thanks for the help yall!

Hi and welcome
could you please attach a sample sheet ( no pics pse) - Click -" Go Adavanced" - Manage attachments with some data and desired results?
Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here is a sample of the worksheet I'm referring to. As described above, sheet1 is the data en mass, sheet2 is simply a key for describing the operations, and sheet3 will be the top three of every production completed. Let me know if yall have any questions! Thanks again for the help.

Attached Files

EMPLOREPORT20170823SAMPLE.xlsx (156.9 KB, 9 views)

Please see attached.

Notes:

• In Sheet3, you need to separate the parameters corresponding to Columns B and C of Sheet1... and each cell needs to be populated as shown in my inserted columns B and C of Sheet1.

• The entries in columns B and C of Sheet3 need to match exactly the entries in column B and C of Sheet1.

• If you have a dynamic table in Sheet1, you should convert your table into an Excel Table, or use Dynamic Named Ranges to allow for expansion. Then use those ranges in the formulas in sheet3.

Formula in Sheet3, D2 is:

=IFERROR(LARGE(IF((Sheet1!$A$1:$A$1000=D$1)* (Sheet1!$B$1:$B$1000= $B2)*(Sheet1!$C$1:$C$1000=$C2), Sheet1!$O$1:$O$1000), COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)),"FIX-IT")

confirmed with CTRL+SHIFT+ENTER not just ENTER as it is an Array Formula. You will notice { } brackets appear around the formula.

Then you copy it down and across the table to get your results.

Hope it helps.

Attached Files

Copy of EMPLOREPORT20170823SAMPLE.xlsx (124.3 KB, 12 views)