R
Richard
Here is the picture of what I'm working with:
http://img121.yfrog.com/i/excel.jpg/
The rank row is the rank of the job according to the static rule, which is
in order from lowest to highest. The formula, just for one of these cells
(C8) is =IF(C2<=$B$1;RANK(C7;$B$5:$U$5;1);"") The starting IF statement is
just to avoid #N/A! errors when there are less jobs than the position
referenced. Row 2 by the way has just numbers 1,2, etc, but are colored white
to hide them without hiding the row.
The order row is the order of the jobs. The numbers here are job numbers,
not ranks. Example formula (D8): =IF(D2<=$B$1;MATCH(D2;$B$8:$U$8;0);"")
Again, referencing the 2nd row just as a function to get the order correct.
As you can see, however, there is a problem. The rule here is that if there
are two jobs that have the exact tie for the static rule, then the job with
the lowest job number goes first. Rank row here shows 641513, skipping 2.
That obviously causes a problem in the order row, since job 5 has nowhere to
go. The order should be 356241.
I've tried several nested IF statements, IF and MATCH statements, even
MATCH(IF(AND())) statements to no avail. So, how does one handle this
situation?
http://img121.yfrog.com/i/excel.jpg/
The rank row is the rank of the job according to the static rule, which is
in order from lowest to highest. The formula, just for one of these cells
(C8) is =IF(C2<=$B$1;RANK(C7;$B$5:$U$5;1);"") The starting IF statement is
just to avoid #N/A! errors when there are less jobs than the position
referenced. Row 2 by the way has just numbers 1,2, etc, but are colored white
to hide them without hiding the row.
The order row is the order of the jobs. The numbers here are job numbers,
not ranks. Example formula (D8): =IF(D2<=$B$1;MATCH(D2;$B$8:$U$8;0);"")
Again, referencing the 2nd row just as a function to get the order correct.
As you can see, however, there is a problem. The rule here is that if there
are two jobs that have the exact tie for the static rule, then the job with
the lowest job number goes first. Rank row here shows 641513, skipping 2.
That obviously causes a problem in the order row, since job 5 has nowhere to
go. The order should be 356241.
I've tried several nested IF statements, IF and MATCH statements, even
MATCH(IF(AND())) statements to no avail. So, how does one handle this
situation?