Forum Discussion
Excel Sort Data by criteria
Hi Skymaze,
ok, then I completely misunderstood you at first. You want the asignment column to be calculated, not the rank column.
I found a relatively complex solution that should do the trick:
=IF(OR(
AND(ISODD(D9),(D9/2)<=$C$5,D9<=($C$5+$C$6)),
AND(ISEVEN(D9),(D9/2)>$C$6,D9<=($C$5+$C$6))),"Receive",
IF(OR(
AND(ISEVEN(D9),(D9/2)<=$C$6,D9<=($C$5+$C$6)),
AND(ISODD(D9),(D9/2)>$C$5,D9<=($C$5+$C$6))),"Ship",""))I'm sure some clever guys will find an easier solution, but for me it seems to work. Just give it a try.
so the employees can be staffed in either department. I want to be able to staff each area based upon need with the best employees (highest rates). But I don't want to give one department the highest rates employees and leave the other department with lower rate employees. I think of it as a total ranked pool that will be split between two areas based upon need. The cells in gold is where I would input my need for each department. Once inputed, the assignment column would calculate and show which employees to select and which department.
Hi Skymaze,
ok, then I completely misunderstood you at first. You want the asignment column to be calculated, not the rank column.
I found a relatively complex solution that should do the trick:
=IF(OR(
AND(ISODD(D9),(D9/2)<=$C$5,D9<=($C$5+$C$6)),
AND(ISEVEN(D9),(D9/2)>$C$6,D9<=($C$5+$C$6))),"Receive",
IF(OR(
AND(ISEVEN(D9),(D9/2)<=$C$6,D9<=($C$5+$C$6)),
AND(ISODD(D9),(D9/2)>$C$5,D9<=($C$5+$C$6))),"Ship",""))
I'm sure some clever guys will find an easier solution, but for me it seems to work. Just give it a try.
- Martin, absolutely perfect! I was on that track but just could not get it to work. Beautifully done sir!