Hi everyone,
in A2:10 i have names
in B2:B10 i have results
in D1: i have a condition
In E2:E10, i need to return the name of the cells in range A2:A10 in a rank order only if their results in B2:B10 is greater than D1
So i created in C2:C10 an if formula to check if every result is greater
Then in D2:D10 i used a rank formula if True
So far i couldnt finish the formula to rank them in order as answer in E2:E10
Is there any formula to continue or to return same answers without the need of other formulas in columns C and D?
Thank you for your help!
in A2:10 i have names
in B2:B10 i have results
in D1: i have a condition
In E2:E10, i need to return the name of the cells in range A2:A10 in a rank order only if their results in B2:B10 is greater than D1
So i created in C2:C10 an if formula to check if every result is greater
Then in D2:D10 i used a rank formula if True
So far i couldnt finish the formula to rank them in order as answer in E2:E10
Is there any formula to continue or to return same answers without the need of other formulas in columns C and D?
Thank you for your help!
Book1.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | 2 | |||||||
2 | B | 3 | True | 3 | L | |||
3 | D | 2 | True | 5 | E | |||
4 | E | 4 | True | 2 | B | |||
5 | G | 0 | false | X | ||||
6 | A | 2 | True | 5 | D | |||
7 | K | 1 | false | A | ||||
8 | M | 0 | false | |||||
9 | X | 3 | True | 3 | ||||
10 | L | 5 | True | 1 | ||||
11 | ||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C10 | C2 | =IF(B2>=$D$1,"True","false") |
D2:D10 | D2 | =IF(C2="true",RANK(B2,$B$2:$B$10),"") |