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.

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.

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.

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.