Find lowest 3 values and sum

[Guest]

John 77 69 73 78 62 69
George 68 73 81 87 89 73
Len 84 77 75 65 87 77

Hi All

With figures above how do I calculate the lowest 3 scores without having to
sort each row? Your assistance is greatly appreciated, i can't wait to try a
suggestion tomorrow at work!!

AussieBec

 

[Guest]

With your data in (Say) A1:F3 put this in H1 and drag down 3 rows to get the
3 smallets values

=small(A1:F3,Row())

Mike

 

[Guest]

correction

=SMALL(A$1:F$3,ROW())

Mike

 

[Pete_UK]

Do you mean John's lowest score (62) plus George's lowest score (68)
plus Len's lowest score (65), or just the three lowest scores whoever
has made them?

Not sure how sorting the rows would help you anyway.

Pete

 

[MartinW]

Hi Bec,

Or do you mean 'each' players 3 lowest scores.
In which case this might help,

Using your example data in A1:G3
In say J1 put this formula
=SMALL($B1:$G1,COLUMN()-9)
Then drag across to L1
Then drag all three cells down to row 3

If you were to start in say M1 the formula would be
=SMALL($B1:$G1,COLUMN()-12)
Then drag across to O1
Then drag all three cells down to row 3

HTH
Martin

 

[Bob Phillips]

=SUM(SMALL(B1:G1,{1,2,3}))

and copy down.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob

Sorry for the delay, I finally try the formula and it works like a treat.
If there is an easy way can you explain the way formula is written I'm quite
curious.

Thanks
AussieBec