#1
|
|||
|
|||
LARGE function with 3 IF statements???
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! |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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.
|
#4
|
||||
|
||||
Please see attached.
Notes:
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Restrict Editing function disable insert textbox function | IanM_01 | Word | 5 | 11-21-2015 02:29 AM |
Help with if then statements | brent chadwick | Word VBA | 35 | 07-25-2015 02:41 PM |
Large Word doc duplicating large sections when I print it. | Kea | Word | 3 | 05-30-2015 02:28 PM |
Using IF statements | stuwoolf | Excel | 2 | 01-10-2015 01:58 PM |
getting IF and OR to work for 3 IF statements | nero6014 | Excel | 3 | 09-16-2014 07:28 AM |