Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Not Lookup
Hello,
I have a table of 20 rows with TIME in Column B, NUMBER OF TARGETS in Column C, and RESULT in Column F. In Column C there are up to 20 values (some will be blank) which can not be in any defined order as they are dictated by the Time in Column B. I wan't to find the four highest results in Column F (probably using the LARGE Worksheet Fn), the corresponding Numbers of Targets from Column C, and find the average number of these Targets. My main problem is that the RESULTS in Column F may duplicate and can not be put in to order, so I don't think I can use LOOKUP. If anyone understood this explaination, do you have a solution? Help Needed Thanks in advance. |
#2
|
|||
|
|||
try
=(large(J1:J20,1)+if(is error(large(J1:J20,2),large(J1:J20,1),Large(j1:j20 ,2))+ if(is error(large(J1:J20,3),if(is error(large(J1:J20,2),large(J1:J20,1),Large(J1:J20 ,2)),Large(J1:J20,3))+if(is error(large(J1:J20,4),if(is error(large(J1:J20,3),if(is error(large(J1:J20,2),large(J1:J20,1),Large(J1:J20 ,2)),Large(J1:J20,3)),large(J1:J20,4)))))/4 I lost track of my parenthesis, so forgive me if you have to play with the Parenthesis. "Jonny" wrote: Hello, I have a table of 20 rows with TIME in Column B, NUMBER OF TARGETS in Column C, and RESULT in Column F. In Column C there are up to 20 values (some will be blank) which can not be in any defined order as they are dictated by the Time in Column B. I wan't to find the four highest results in Column F (probably using the LARGE Worksheet Fn), the corresponding Numbers of Targets from Column C, and find the average number of these Targets. My main problem is that the RESULTS in Column F may duplicate and can not be put in to order, so I don't think I can use LOOKUP. If anyone understood this explaination, do you have a solution? Help Needed Thanks in advance. |
#3
|
|||
|
|||
Yes, I have that to find the average of the top 4 in column F.
So If the highest is F9, i need the value of C9, If the second highest is F14, i need the value of C14, etc. then the average of C9,C14... Then I will have the average of the four highest in F (which i have) and the average C value of these (which i need) Does that clarify a little! "bj" wrote: try =(large(J1:J20,1)+if(is error(large(J1:J20,2),large(J1:J20,1),Large(j1:j20 ,2))+ if(is error(large(J1:J20,3),if(is error(large(J1:J20,2),large(J1:J20,1),Large(J1:J20 ,2)),Large(J1:J20,3))+if(is error(large(J1:J20,4),if(is error(large(J1:J20,3),if(is error(large(J1:J20,2),large(J1:J20,1),Large(J1:J20 ,2)),Large(J1:J20,3)),large(J1:J20,4)))))/4 I lost track of my parenthesis, so forgive me if you have to play with the Parenthesis. "Jonny" wrote: Hello, I have a table of 20 rows with TIME in Column B, NUMBER OF TARGETS in Column C, and RESULT in Column F. In Column C there are up to 20 values (some will be blank) which can not be in any defined order as they are dictated by the Time in Column B. I wan't to find the four highest results in Column F (probably using the LARGE Worksheet Fn), the corresponding Numbers of Targets from Column C, and find the average number of these Targets. My main problem is that the RESULTS in Column F may duplicate and can not be put in to order, so I don't think I can use LOOKUP. If anyone understood this explaination, do you have a solution? Help Needed Thanks in advance. |
#4
|
|||
|
|||
I misread your post
If there is a tie for 4th would the same value be in C or is there a tie breaker? =if(iserror(large(F1:F20,5), Tiebreaker, =sumproduct(--(rank(F1:F20)<5),C1:C20))/4 "Jonny" wrote: Hello, I have a table of 20 rows with TIME in Column B, NUMBER OF TARGETS in Column C, and RESULT in Column F. In Column C there are up to 20 values (some will be blank) which can not be in any defined order as they are dictated by the Time in Column B. I wan't to find the four highest results in Column F (probably using the LARGE Worksheet Fn), the corresponding Numbers of Targets from Column C, and find the average number of these Targets. My main problem is that the RESULTS in Column F may duplicate and can not be put in to order, so I don't think I can use LOOKUP. If anyone understood this explaination, do you have a solution? Help Needed Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |