Hi.
So many questions about finding highest numbers, but all solutions I have found just returns them. I need it to be displayed as value 1 or 0 if non highest.
Question 1. See table. The source range is B2:K4, result range - B6:K8. I need to find any occurrences of first and second highest values in every row and get it as value 1 in result block like it done on my example.
Question 2. Like in first question but the rows are longer - from B to CW and contain 100 numbers. I need to find half highest of them and get it as value 1. Due to multiply occurrences of some numbers it cannot be always 50 and 50, so in such cases closest to 50 over 50 set must be chosen (51,52,53 etc).
Thanks.
So many questions about finding highest numbers, but all solutions I have found just returns them. I need it to be displayed as value 1 or 0 if non highest.
question.xls | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ||||||||||||||
2 | 3 | 4 | 2 | 5 | 4 | 6 | 2 | 3 | 7 | 5 | ||||
3 | 5 | 3 | 4 | 5 | 8 | 7 | 3 | 6 | 4 | 7 | ||||
4 | 6 | 4 | 4 | 5 | 3 | 6 | 4 | 2 | 3 | 4 | ||||
5 | ||||||||||||||
6 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | ||||
7 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | ||||
8 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | ||||
Sheet3 |
Question 1. See table. The source range is B2:K4, result range - B6:K8. I need to find any occurrences of first and second highest values in every row and get it as value 1 in result block like it done on my example.
Question 2. Like in first question but the rows are longer - from B to CW and contain 100 numbers. I need to find half highest of them and get it as value 1. Due to multiply occurrences of some numbers it cannot be always 50 and 50, so in such cases closest to 50 over 50 set must be chosen (51,52,53 etc).
Thanks.