New Priority Column

[ceemo]

I have the following

name Meals days work-sales
bob....1............1.............3
pete...3.............1............3
jane...2..............2...........5
jill......5.............3.............5
terry..4 ..............1............4

I would like to add a new column that gives a priority column on the
end of the table. The highest priority will be number one and will goto
the person with the highest number of work-sales then days then highest
meals.

The table would look like

name Meals.....days.....work-sales...Priority
bob....1............1.............3...........5
pete...3.............1............3...........4
jane...2..............2...........5............2
jill......5.............3.............5...........1
terry..4 ..............1............4..........3


I would like to do this using a formula rather than a script

 

[vezerid]

Assuming worksales in D15, days in C1:C5, meals in B1:B5, you could
use the following:

=SUMPRODUCT(--(100*$D$1:$D$5+10*$C$1:$C$5+$B$1:$B$3>100*D1+10*C1+B1))+1

Does this help?

Kostis Vezerides

 

[Trevor Shuttleworth]

Ceemo

this works on your sample data, though it needs an intermediate column. I'm
not sure if it could be combined ... but I couldn't work it out.

in Column E, cell E2, put the fomula:

=RANK(B2,B:B,1)+RANK(C2,C:C,1)+RANK(D2,D,1)

in column F, cell F2, put the formula:

=RANK(E2,E:E,0)

Column F is the Priority that you wanted.

Drag both formulae down. This gave the right result for your data though I
don't know if it will give you what you want on a grander scale. But, give
it a go

Regards

Trevor

 

[Trevor Shuttleworth]

OK ... that's the way to do it in one go !

I needed to tweak it a little ... think there's a typo ($B$3) and also
assumed the first row was a header.

Regards

Trevor