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 D1:D5, 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: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


"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=534708
>

[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


"vezerid" <[email protected]> wrote in message
news:[email protected]...
> Assuming worksales in D1:D5, 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
>