Help with formula please - Thanks

[Eamon]

Hi,



I would be most grateful for any help with the following please.



In Column D4 to D44 I have numbers such as.

100

101

49

58

78

98

82

77

104



In Column E, I want a formula that will return the top six "numbers" giving
10 to the highest number and 0 to any number not in the top six i.e.

104 10

101 9

100 8

98 7

82 6

78 5

77 0

58 0

49 0



Again from the numbers in Column D, In Column G, I want a formula that will
return the bottom six "numbers" giving 10 to the lowest number and 0 to any
number not in the bottom six i.e.

49 10

58 9

77 8

78 7

82 6

98 5

100 0

101 0

104 0



Finally in Column M i want a formula that will count the no. of Cells in
Column "E, G, J and K" that contain a number = to or greater than 5.



Thanks,



Eamon

 

[Bob Phillips]

In E4

=IF(NOT(ISNA(MATCH(D4,LARGE($D$4:$D$12,{1,2,3,4,5,6}),0))),INDEX({10,9,8,7,6
,5},MATCH(D4,LARGE($D$4:$D$12,{1,2,3,4,5,6}),0)),0)

and copy down

 

[Eamon]

Bob,

Thank you very much for your help it is much appreciated.

Also require help with this please...
Finally in Column M i want a formula that will count the no. of Cells in
Column "E, G, J and K" that contain a number = to or greater than 5.

Thanks again,

Eamon

 

[Bob Phillips]

Eamon,

Usingh a technique Domenic posted a few days ago

=SUMPRODUCT(TRANSPOSE(ISNUMBER(MATCH(COLUMN(E1:K1)-COLUMN(E1)+1,{1,3,6,7},0)
))*ISNUMBER(E4:K2000)*(E4:K2000>5))

 

[Eamon]

Bob,

Thanks again for your help but the formula is not returning what I want,
probably due to how I worded the original question, so please let me
explain...

In M1, I am looking to enter a formula that will look in E1, G1, J1 and K1
and return the number of times a number equal to or greater than 5 is found
i.e.
E G J K M
10 0 5 7 3
0 0 4 5 1
10 8 7 5 4

Hope this better explains what I am trying to do.

Eamon

 

[Bob Phillips]

Okay, slight variation, the original added them all in one sweep

=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(E1:K1)-COLUMN(E1)+1,{1,3,6,7},0))*ISNUMBER
(E1:K1)*(E1:K1>5))

 

[Eamon]

Bob,

Thanks for everything.

Just made a small change to your formula and it appears to be working Ok.
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(E1:K1)-COLUMN(E1)+1,{1,3,6,7},0))*ISNUMBER(E1:K1)*(E1:K1>=5))

Best regards,

Eamon

 

[Bob Phillips]

LOL. When I wrote the first version I used >=, but then I got it in my mind
you asked for just greater than, so I changed it at the last minute.