OVER 2 YOU 174 (30/03/04)

WEEKEND WOES

I am using Excel Chart to graph a price series against time. However, the time axis of the data is for business days only, i.e. it excludes weekends. Unfortunately Excel Chart insists on adding back the weekends and so distorting the shape of the graph. How can I avoid this?

Howard Skipp, Guildford

Excel looks at the dates and assumes that all dates form part of the series, whether or not they are entered in the spreadsheet. Assuming that the dates form the X-axis, as you create the chart and get to Step 3 (Chart Options) of the wizard, choose the Axes tab and click in the radio button next to Category in the Primary Axis section.  Excel will then regard the dates as text. To correct previously created charts, make sure the chart is active, then either right click the chart area or click Chart from the menu bar and choose Chart Options to take you to the same dialogue box.

Maggie Sloss, via email

If Howard Skipp takes his original data set, assuming data entered in columns, and sets up a  'Dummy, same number of rows' column next to his L/H column (Category X axis), filling the cells with weekday names i.e. Mon, Tue... relevant to the date produces a 3 column data set.

Column heading layout would be  'Dayname/Date (dd/mm/2004) / Price'.  

To create a new chart, select all 3 columns, select Chart Wizard > Line > Finish. The chart will now set up the 'Category X axis' with Date, Day name for each point plotted. If he wishes not to have the day name included, just clear the Dummy column of all entries. Excel now reserves space in the program so that any info can be re entered, maybe additional coding for that particular date without having to re-use the Chart Wizard. I found that the Date format style most suitable for charts is  'dd.mm.yyyy'

Joe F Smith, via email

It can easily be done by 'Hiding' all those data rows/columns on the spreadsheet, which are not required in the chart. MS Excel will automatically exclude that data from the chart.
Noor Choudhary, via email

Assuming that the dates are required in a horizontal row, the following basic formula will do the trick:
=DATE(YEAR(WORKDAY(37987,COLUMN())), MONTH(WORKDAY(37987,COLUMN())),
DAY(WORKDAY(37987,COLUMN())))

Enter it in the left-most column then copy it to any number of adjacent columns. The WORKDAY() function is included in the 'Analysis ToolPak' add-in which can be installed from the Excel CD by selecting 'Tools > Add-ins' from the menu bar. The number 37987 used above is the serial number used by Excel (Windows version) to represent 1st January 2004.
The Column() function provides the number of the column (e.g. column A is
number 1, B is 2 etc.).

The above formula entered in column B gives the date 05/01/04, i.e. 2 working days after 1st January 2004.  The serial number can be amended to suit the desired start date and column in Howard's spreadsheet. To get the serial number of any date, enter the date in any cell and format the cell as 'General'. If the date series is required to be shown vertically, use the function ROW() instead of COLUMN().
Peter Astill, via email

To prevent Excel from leaving gaps in a chart, corresponding to weekends and other non-working days, highlight a whole column, choose Cells from the Format menu, and select "Text" before entering the dates in this column. Highlight the column containing the financial data, etc., and go to Insert > Chart and choose the "Line" option. For the Category axis labels, pick the column containing the dates. Change the text orientation to vertical and choose a suitable font size to allow all the dates to be printed.
C. J. Evans, via email

MAC TAX

There are several tax calculation programs for the PC, such as Tax Calc and Microsoft Money etc. Can anyone recommend an equivalent program suitable for a Mac G3 user?
Kevin O'Rourke, Milton Keynes.

If your reader is referring to PAYE then the Inland Revenue (http://www.inlandrevenue.gov.uk/) issue a CD-ROM, which is compatible with G3 MACS Classic OS as well as PC's.
Philip Harris, via email

Unfortunately the vast majority of tax software for the Mac is American though I would be very surprised if one or two of them were not configurable for the UK market. There’s a good overview of the current crop of programs on the Mac Review Zone web site at: http://www.macreviewzone.com/

html/reviews/magazine/software/finance.php

Simon Whittard, via email

DISTANCE LEARNING

I am a very frequent traveller and like to record the distances I travel. I used to use my old Psion 5MX, enter my location and destination and it would give me the total distance in miles. My new iPaq does not have this function. Can anyone suggest any suitable software, or a web site, that can calculate the distance between 2 locations, worldwide?
David Hardy, via email

Your correspondent should visit www.pdawin.com, where he can purchase a program called "Globe" for his iPaq. It has a database of world cities; others can be added with co-ordinates, and Globe will show him the location of the city on the world globe. It shows the date and time at the selected city, plus the sunrise and sunset times, the day/night rotation of the globe and using the distance meter it will give the directly distance in miles or kms, plus the compass angle between two selected cities. It also has a currency converter. Mine came free with the iPaq 3970 so I am sure the price is reasonable.

Dave Craddock, via email

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

Download a freeware program called MusicMatch Jukebox (http://www.musicmatch.com/) and CoolEdit 2000 (now Adobe Audition). Use MusicMatch Recorder to record tracks from CDs then open "file location" and drag and drop these tracks to a folder created in CoolEdit. When a track is opened in CoolEdit the program gives a number of possibilities, including Time/Pitch, which reduces or increases Tempo.  The program has the option to change the tempo without affecting the pitch so Sopranos do not become bass-baritones and it doesn’t sound like an old wind-up gramophone.

After preparing a number of different tracks (or the same track at different tempos) they can be dragged and dropped into MusicMatch BurnerPlus to record a CD.

Robert A. Everett, via email

CAN YOU HELP?

Is there any device that will block the use of irritating mobile phones by loud and selfish users in public places?

Hendrik Woolf

I am creating a list of 'subjects' in a column in Microsoft Access and find that it easy enough to sort the subjects alphabetically, but some of the items also contain numbers - i.e. FPO 99.

I cannot work out how to get the numbers in order as well as the alphabetical subjects - can anyone help?

Alan Crozier, via email