OVER 2 YOU 169
I work out the results at
my club for yacht race series where competitors are allowed to discard their
worst 1, 2 or 3 results depending on the number of races in the series.
Normally, I do this by hand but would like to use Excel to do it automatically.
I know how to discard one worst result using the MAX function, e.g.
SUM(A1:E1)-MAX(A1:E1), but I would be grateful if someone could suggest a formula for discarding the highest 2 or 3 results.
Jim Newman, via email
Try the following formula to automatically discard the lowest and 2nd lowest results:
=SUM(a1:e1) - MIN(a1:e1) - SMALL(a1:e1,2)
In this example Min(a1:e1)
will provide the lowest result and Small(a1:e1,2) will provide the 2nd lowest
result. You can use the Small formula to discard any number of lowest result
e.g. if you also wanted to discard the 3rd lowest result, simply modify the
above formula as below:
=SUM(a1:e1) - MIN(a1:e1) -
SMALL(a1:e1,2) - SMALL(a1:e1,3)
Subhash Sharma, via email
I would suggest that Jim
Newman try "Sailwave Sailing Scoring Software" available from www.sailwave.com. This software has been written by Colin
Jenkins who is a sailor. In my opinion Sailwave is the best solution and does everything you need. Colin
regularly updates the software to take account of the latest changes to RRS as
well as adding new features. The program produces printed reports and can be
customised to generate HTML allowing you to publish results on your club web
site. Colin responds very quickly to requests for assistance, there is also an
active user group the members of which are very helpful. All this for free. Forget the Excel based systems, I found that most of them required me to update to a newer version of Excel at considerable expense.
Hugh Davies, via email
Assuming there are eight
races in the series then use the following formulae, adjusting numbers and column
letters to suit:
This produces positions
after eight races with three discards. Then select all yachts across to
positions and select 'Data', 'Sort' Column Q. If there are tied positions
these can be manually adjusted after count-back and then re-sorted.
Alan Chidgey, via email
The LARGE function in Excel
works in a similar way to the MAX function except that it returns the n-th largest value in a data set rather than just the largest. The LARGE function syntax works like this: LARGE(array, n), where array is the list of values or the cell range of data for which you want to determine the n-th largest value, and n is the numerical position starting from the largest value in the list or cell range of the data to be returned. So using the same approach as Mr Newman used to deduct the highest score from his total, the formula:
This will sum the range A1
to A5 and deduct the three largest values in the range. The SMALL function works in the same way as the LARGE function, but returns the
n-th smallest value.
Ian Fiander, via email
We produce a sailing
results program called Hal's Race Results, which does all the things that Jim
requires and a lot more, as well. Details can be found on our website www.halsraceresults.com and he can order a
sample copy from the website.
She Taylor, Halís Race
Results, via email
The result could be obtained using an Excel Macro:
1. Select Tools/Macro/Visual Basic Editor
2. then Insert/Module
3. Create the following function:
Function mySum(myRange As Range, myDiscard As Integer)
Dim X, Y, myMax, MaxPos As Integer
X = 0
For Each cell In myRange.Cells
ReDim Preserve myValues(X + 1)
myValues(X) = cell.Value
X = X + 1
For Y = 1 To myDiscard
myMax = 0
For X = 0 To myRange.Cells.Count
If myValues(X) > myMax Then
MaxPos = X
myMax = myValues(X)
myValues(MaxPos) = 0
mySum = 0
For X = 0 To myRange.Cells.Count
mySum = mySum + myValues(X)
On the Spreadsheet you can now use the function =mySum(Range,Number to Discard)
where 'Range' is the set of results you wish to sum and 'Number to Discard' is
the number of high results you wish to discard.
Neil Watson, via email
I have devised a
spreadsheet having the appropriate calculations. It is not too elegant, but if
I find a better solution I'll let you know. What I'd really like to do is to
copy each row of results and then sort the entries into numerical sequence. The
calculation would then be easy. However, I have not found a way of doing this
with my version of Excel, so my 'longhand' solution is:
ROW F: Calculates the
highest number in Rows A to E.
ROW G: Calculates how
many times this maximum number occurs.
ROW H: Calculates
what is the second highest number.
ROW I: Calculates how many
times the second highest number occurs.
ROW J: Calculates the third
ROW K: Equals the highest
number to be discarded
ROW L: Calculates the
second number to be discarded.
ROW M: Calculates the third
number to be discarded.
ROW N: Calculates the sum
of the race scores.
ROW O: Calculates the sum
of the scores with the highest score discarded.
ROW P: Calculates the sum
of the scores with to highest two discarded.
ROW Q: Calculates the sum
of the scores with the highest three discarded.
Trevor Woodcock, Houghton
on the Hill
We are interested in
American Square Dancing. At times, with people of varying aptitudes --
especially beginners, or when learning a new set of steps -- we need to be able
to vary the tempo of the music by between plus or minus 5 to 30%. This was easy to do with cassettes, Philips
produced a deck with variable speed playback, but CDs present a problem with
their essentially constant speed playback. Can anyone provide a solution,
possibly by modifying a CD deck or transferring our music library to a PC?
Roger Avery, via email
We have a karaoke machine at home that plays CD's
(the cost was about £150 three to four years ago but you
can get them for quite a lot less these days).
It's a standalone machine that plays tapes, CD Graphic
discs and also normal CD's. There is a control on the
machine, which turns the tempo up or down.
Lynn Jones, via email
Further to previous
questions about disposing of old and obsolete PCs and piles of unwanted
CD-ROMs, I am seeking creative or ecologically sound suggestions as to what to
do with a growing collection of mice, speakers and keyboards. The chap at the
local charity shop said they already had more than enough of them cluttering up
the place and I am reluctant to throw them away in the general rubbish at the
amenity tip. Are they good for anything, can they be recycled?
Kelly Michaels, via email
I donít know about disposing of keyboards but when
visiting a Scottish harbour, I noticed many of the boats
had old CD-ROMs slung between the masts - presumably
to keep the sea gulls at bay
Ian Jodrell, via email
CAN YOU HELP
I'm developing a website to
run from home in my spare time for Italians learning English. While confident enough to put together a simple but functional site I have no idea how to set up a subscription system that would allow say six months' access for £20. Can anyone recommend a reasonably
priced, reliable and easy to use method to accomplish this?
Gerard Dorrity, Turin,
I am one of 8 golfers going
on a 7-day golfing holiday. We want to play 2 Fourballs each day. Each
golfer must have a different partner each day. In any Fourball,
it should be possible to ensure that each golfer plays the minimum number of
times with other players over the 7 days of play. Can anyone come up with
an Excel formula to calculate each day's parings?
Graham Jones, via email