OVER 2 YOU, 018 (22/02/01)

1950's SONGS

I am looking for two songs of the late 50's and can only remember a line or two. Are there any websites that can help in finding the title and the whole song?
Doug Plummer

I'd suggest 'www.lyrics.ch'.  Invaluable for quizzes and the like, and has never failed to provide an answer.  Watch out for American spellings though!

Chris Brooks,

You don't really need to know about any specific websites: the easiest way to find the lyrics (if they exist on the web) is to go to a search engine like Googol or AltaVista, and actually key in the text you're looking for (don't forget to put the line in inverted commas). That way, you'll find the lyrics wherever they are - on a dedicated website or not.

This method almost invariably works for me when I'm looking for a particular poem, and even prose on occasion.

Peter Simon,

Try www.cddb.com, which is a database of track listings. If he fails there he could try "musicimports .com" which specialises in "hard to find " titles, using its Power Search and entering the words he knows in the Title slot on the offchance that they would reflect the title.
Patrick Cooney,

Afraid I don't have a website to offer, but a couple of friends and I have a pretty extensive knowledge of 50's and 60's music trivia!  If Doug wants to E-mail me with the lines, male or female vocalist or group, US or UK, or anything else that might help, we'll be glad to help if we can.
Tim Gott,

CRICKET

Could you suggest an Excel formula that will add up Cricket Overs and part Overs, i.e. 5.4 + 3.3 needs to add up to 9.1 and not 8.7 as would happen using the normal number format?

Steve Shaw.

Steve Shaw should not try to add his overs in one complex formula.As an example and assuming that three cells hold the overs to be added (A,B,C) break the method down as follows:

Cell X:  =INT(A)+INT(B)+INT(C)
Cell Y:  =(SUM(A,B,C)-X)/0.6
Cell Z:  =ROUND((Y-INT(Y))*0.6,1)
Answer:  =X+INT(Y)+Z

Cell X contains the sum of the whole overs only.
Cell Y uses the Cell X result to determine the sum of any part overs and shows the result as overs and decimal fractions of overs.Cell Z takes the fraction part of Cell Y and converts it back to the 0.x layout required.

Once in place, the lines or columns containing X,Y and Z can be hidden so the calculation does not mess up the display or print.
Andrew Kinnon,

Steve Shaw could deal with his bowling figures by converting each of the overs and part overs into balls bowled, adding up the total balls and converting the total back into overs and part overs.

Convert overs (A1) to balls: =INT(A1)*6+MOD((A1*10),10)
Convert balls (A2) to overs: =INT(A2/6)+MOD(A2,6)/10

Combining these, with 5.4 (B1) and 3.3 (B2), the formula for adding the two together to make 9.1 would be:

=INT((INT(B1)*6+INT(B2)*6+MOD((B1*10),

10)+MOD((B2*10),10))/6)+(MOD((INT(B1)*
6+INT(B2)*6+MOD((B1*10),10)+MOD((B2*10),10)

),6))/10
Ian Lowe,

In the example given, 5.4 and 3.3 are not real numbers. It is necessary to convert them to real numbers to sum them then to convert them back again. Use =((A1-INT(A1))/0.6+INT(A1)) and =(((B1-INT(B1))*0.6+0.01+INT(B1))) respectively. A "working column" is required to hold the results of the first calculation and the sum of these results; this column can be placed outside of the printed area. The cell(s) containing the final results should be formatted to display only one decimal place.

Peter Cockerill,

I think the following formula should solve Steve Shaw's problem where cells A1 and B1 contain the numbers to be added together.

=IF((A1-INT(A1))+(B1-INT(B1))>=0.6,A1+B1+0.

4,A1+B1)

If the fraction part of the sum is greater than 0.6 you need to increase the units by one and decrease the tenths by 0.6.  Adding 0.4 to the total achieves this. Of course, this only works for two numbers.  If you want to add more than two numbers the equation starts to get very long.
Nigel Hate,

Perhaps the easiest solution to Steve Shaw's problem of adding Cricket Overs and Part Overs is to take advantage of the fact that there are 6 balls in an over and 60 minutes in an hour.

Format the cells in Excel as Time (e.g. Type 13:30) and enter the Overs and Part Overs as hours and minutes. Using Steve's example, put 5:40 in cell A1 and 3:30 in cell A2. Summing A1+A2 will give the answer 9:10.

Don Brealey,

Steve Shaw seems to me to make the common error regarding part overs in expressing the balls bowled as .4 or .3 giving the impression of a decimal fraction which they are not. If 4 balls is shown as .6 and 3 balls as .5 then 5.6 and 3.5 adds up to 9.1.

Roy Munton,

Select two columns, A and B. In the first enter the whole numbers and in the adjacent column enter the fractions.

To add the overs including fractions as fractions enter this formula somewhere at the foot:

                                =((SUM(A1:A10)*6+SUM(B1:B10))/6)

assuming there are 10 overs to be added up. If more or less, change the formula as necessary.

David Hodson,

If the number of overs in the original format are placed in the first column of a spreadsheet (I used Excel) , the total number of runs can be worked out in the second column using  =INT(A1)*6+MOD(A1,1)*10   (the MOD function gives the remainder when the score is divided by one). This formula can be copied downwards as often as needed. The total number of runs in the second column can then be evaluated using the SUM function. If this total is in cell B12 say, it can then be transformed back to the original format  for overs using    =INT(B12/6)+MOD(B12,6)/10
Bob Herbert,

The following formula appears to work assuming the two numbers to be added are in cells A2 and B2. All cells should be set for one decimal place for neatness

=(IF((A2+B2)-TRUNC(A2)-TRUNC(B2)-0.5<=

0,A2+B2,(A2+B2)+0.4)) 

Mike Bidgood,

Whilst it is possible to solve his problem using one formula it is much less complicated to split this up into two parts and hide the column of intermediate calculations. Assuming that the overs 5.3, 6.4, 3.4 are in cells B4, B5, and B6 respectively then enter in cell C4 the formula

                        =INT(B4)*6+(B4-INT(B4))*10

This converts the overs into balls. So cell C4 becomes 33.

This formula can be copied into C5 and C6, which will result in the entries 40 and 22. In C7 enter the formula

                        =Sum(C4:C6)

This will total the number of balls 95.  In cell B7 enter the formula

                        =CONCATENATE(INT(C7/6),".",MOD(C7,6))

This will then convert the number of balls back into overs giving the result 15.5. To tidy up the sheet the column C may be hidden.

Mike Scott,

To add Cricket Overs you must first convert them so that they mathematically represent the correct fraction of an Over.  For example, 5.4 Cricket Overs is the same as saying 5 Overs plus 4 Balls out of 6 (or 4/6), which is written mathematically as 5 4/6 Overs. In your example you give 5.4 and 3.3 Overs which is 5 4/6 and 3 3/6 Overs.  Adding these two numbers we get 9 1/6 which can easily be converted back to the cricket format of 9.1

Excel procedure. 

Prior to adding, convert Cricket Overs individually with this formula:

    =INT(Overs) + 5 * MOD(Overs,1) / 3

Add all the converted Overs together (can more more than 2) to give the Result in mathematical format.

Then convert the Result back into Cricket format by this Excel formula:

    =INT(Result) + 3 * MOD(Result,1) / 5

Excel Visual Basic Option

Alternatively open your Excel Cricket Workbook, hit ALT+F11, Choose Insert, Module, and type this in the top right window:

    Function ADDOVERS(over1 As Currency, over2 As Currency) As Currency        

Dim result1 As Long, result2 As Long, totalovers As Long
        result1 = 6 * Int(over1) + (10 * over1) Mod 10
        result2 = 6 * Int(over2) + (10 * over2) Mod 10
        totalovers = result1 + result2
        ADDOVERS = Int(totalovers / 6) + (totalovers Mod 6) / 10
    End Function

Now close the Visual Basic Window and return to Excel.  You have added a new function only to the Cricket Workbook called ADDOVERS() and it will do it all for you:

    =ADDOVERS(5.4,3.3)    gives 9.1

or

    =ADDOVERS(A1,A2)

etc

I am a complete novice at Visual Basic but I am sure someone else could write a function to add a range of cells.

Gary F.Conlan,

TENNIS

I am the captain of a tennis club and for many years have been trying to find an easy (or even easier) way to organise a mixed doubles "American" tournament. The aim in each round is for different partners and opposition.  Each man should play against every other man and lady and also with every lady as a partner.  Similarly each lady should play against every other lady and man and with every man as a partner. Theoretically it is, of course, impossible since for "N" men and ladies there need to be "N" matches for each man to play against and with every lady but only "N-1" matches for each man to play against every other man. The idea is to come up with the best combination of partners and opposition with the least repetition for 5 to 12 couples over 6 or 7 rounds.

Ken Walker

My club (Phoenix Tennis Club, Colchester) uses 'Tennis5', which we commissioned and which can be downloaded from www.keinton.demon.co.uk/tennis/. This program will cater for any number of players, rounds and available courts and we have found it very satisfactory and flexible.

Bob Light,

CAN YOU HELP?

Despite the Internet's reputation for being able to supply every conceivable human desire, I am unable to find anyone selling hedgehogs or slow worms. These are not for occult purposes but as a green alternative to slug pellets. As a gardener who lives in London, I am desperate to deter monopods from eating my bulbs.
Ian C, via email

I have a school photograph 80 cms wide and 15 cms deep containing some 300 faces in seven rows. I would like to name everybody L to R style, in a caption using my pc. As a complete novice, I haven't a clue how to do it.  All I do know is that if I can feed envelopes into my printer longtitudinally and see addresses printed latitudinally, an 80 cms caption  would seem possible.

Ronald Kershaw, via email

I am a craftsman goldsmith specialising in the design and handcrafted commissions.  Does anyone know of any software that I could use to show my clients say, for example, a variety of precious stones, settings, basic designs etc and perhaps also a facility to add my own artistic development.  In this way I clients would be able to see the piece almost in reality. I know it can be done with clothes and hairstyles and feel sure the technology will have been used elsewhere.

John D. Vaughan, via email