Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH/IF/OR/AND statements not what I need apparently
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH/IF/OR/AND statements not what I need apparently
Definitely still need help on this!
"Richard" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with INDEX and MATCH statements. | Excel Worksheet Functions | |||
Multiple match statements? | Excel Discussion (Misc queries) | |||
match statements | Excel Discussion (Misc queries) | |||
Match/Lookup Statements | Excel Worksheet Functions |