Best 4 months Commission

[initss]

Quite new to Excel functions I hope this is achievable.

I have a spreadsheet of employees along with their commission to date.
APR MAY JUNE JULY AUGUST SEPTEMBER
Fred 10 20 30 40 50 60
Bill 20 40 10 60 70 20
Jim 30 10 50 90 10 0
Harry 0 100 20 30 0 40

I need to give the sum of their worst 4 performances and produce a
ordered list.

So the output:

Fred 100
Bill 90
Jim 50
Harry 50


Any help appreciated

 

[Trevor Shuttleworth]

=SUM(SMALL(B2:G2,{1,2,3,4})) and drag down

then sort on the result

similarly for LARGE

=SUM(LARGE(B2:G2,{1,2,3,4}))

Regards

Trevor

 

[Frank Kabel]

Hi
I would first add a helper column which stores the sum for the worst 4
performances. Lets say your data is in column B:G use the following
array formula in H2 (the first data row). The formula has to be entered
with CTRL+SHIFT+ENTER
=SUM(SMALL(B2:G2,{1,2,3,4}))
and copy this formula down

now you can use this column for sorting

 

[Trevor Shuttleworth]

Frank

I don't think you need to Array Enter that formula ... doesn't hurt but it's
not needed.

Regards

Trevor