help formula (ranking)

[Eddy Stan]

Hello all,

please paste the data and sort by 'result as"
Dues SETS Rank 60P Rank 180P Result as
1 3,954,755 SET3 43 36 SET3 1
2 1,009,290 SET2 11 8 SET2 1
3 204,452 SET3 61 37 SET3 2
4 2,986,730 SET1 8 11 SET1 3
5 2,505,872 SET1 6 6 SET1 2
6 120,000 SET4 81 54 SET4 2
7 1,983,047 SET3 67 40 SET3 3
8 10,870,882 SET1 1 1 SET1 1
9 2,725,486 SET2 16 14 SET2 4
10 462,446 SET4 81 54 SET4 1
11 1,210,462 SET2 13 13 SET2 3
12 46,750 SET2 30 18 SET2 5
13 477,155 SET2 17 12 SET2 2
14 2,299,668 SET3 77 46 SET3 4

i need formula to get "result as", i put them manually.
conditions are:
use 60+ rank from 1 to 10, to give me SET1 01,02,....
use 180+ rank and dues, evaluate (>100k) and give me SET2 01,02...
use 180+ rank and dues, evaluate (<100k) and give me SET3 01,02...
use dues and give SET4 01,02...

thank you

 

[JLatham]

I think we need a more detailed list of the rules that are used. It looks
like 'Result As' simply picks up the entry in the 'SETS' column and then adds
a number from 1 to 5 based on some combination of 'Dues' and the two
'Rank...' columns. We need the rules to determine that value to help you,
for example:

if Dues > 100K and Rank60P > 30 then = 1
if Dues > 100K and Rank 180P > 10 then = 2
or perhaps
if Dues > 100K and Rank 60P > 20 And Rank 180P > 40 Then = 1

If we have the complete set of rules we can help better. Or explain if you
just come up with 1, 2, 3, 4 and 5 based on the totals of Rank60P and
Rank180P.