OVER 2 YOU 169 (24/02/04)

YATCH RACES

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:

Score  =(D3:K3).

Discard 1  =IF(COUNT(D3:K3)>5,MAX(D3:K3),0) 

Discard 2  =IF(COUNT(D3:K3)>6,MAX(D3:K3,2),0)

Discard 3  =IF(COUNT(D3:K3)>7,MAX(D3:K3,3),0)

Total  =SUM(L5-M5-O5)

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:

=SUM(A1:A5)-LARGE(A1:A5,1)-LARGE

(A1:A5,2)-LARGE(A1:A5,3)

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 myValues()
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

Next cell
For Y = 1 To myDiscard
myMax = 0
For X = 0 To myRange.Cells.Count
If myValues(X) > myMax Then
MaxPos = X
myMax = myValues(X)
End If
Next X
myValues(MaxPos) = 0
Next Y
mySum = 0
For X = 0 To myRange.Cells.Count
mySum = mySum + myValues(X)
Next X
End Function

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.
  Formula =MAX(A1:E1)

ROW G:  Calculates how many times this maximum number occurs.
  Formula =SUM(IF(A1:E1=F1,1,0))

ROW H:  Calculates what is the second highest number.
  Formula =MAX(IF(A1:E1<F1,A1:E1,0))

ROW I: Calculates how many times the second highest number occurs.
  Formula =SUM(IF(A1:E1=H1,1,0))

ROW J: Calculates the third highest number.
  Formula =MAX(IF(A1:E1<H1,A1:E1,0))

ROW K: Equals the highest number to be discarded
  Formula =F1

ROW L: Calculates the second number to be discarded.
  Formula =IF(G1>1,F1,H1)

ROW M: Calculates the third number to be discarded.
  Formula =IF(G1>2,F1,IF(G1>1,H1,IF(I1>1,H1,J1)))

ROW N: Calculates the sum of the race scores.
  Formula =SUM(A1:E1)

ROW O: Calculates the sum of the scores with the highest score discarded.
  Formula =N1-K1

ROW P: Calculates the sum of the scores with to highest two discarded.
  Formula =N1-K1-L1

ROW Q: Calculates the sum of the scores with the highest three discarded.
  Formula =N1-K1-L1-M1

Trevor Woodcock, Houghton on the Hill

SQUARE DANCING

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

SCRAP PARTS

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, Italy

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