OVER 2 YOU, 054 (01/11/01)

UK EARNINGS

Can you please tell me where I can find information about the distribution of  earnings in the UK? I would ideally like to be able to see how many people are in each group by  increments of, say, £10,000 up to £100,000 and £100,000 from there on.

Mr Pierson, via email

For this kind of information Mr Pierson should go to:
http://www.inlandrevenue.gov.uk/stats/

s3_personal_incomes_3.pdf

where he will find all he needs and a good deal more. He should download the pdf file and can then refer to it at his ease, offline.  He will need an Acrobat reader which is available online for free.
J. Peter Rauch, Wonersh

BIRD RINGS

I am working with a large spreadsheet on which is entered data on netted birds. The data is collected very quickly because birds have to be released in a short time. Consequently there are entry errors particularly the alphanumeric sequence ring number. I want to find all instances of any ring number being entered more than once. I can of course find a particular number duplicate by using FIND but I think there ought to be a way of finding any string that appears more than once in a column.  At the moment I sort by the ring number column and then search by eye.
John Bonell, via email

John Bonell's ring number problem can be solved with the application of a bit of Boolean Logic, easier than it sounds.

1.      Sort the WHOLE spreadsheet by the ring number column.

2.      Insert a new column to the right of the ring numbers.

3.      In the new column adjacent to the second ring number enter =OR((B2)=(B3),(B3)=(B4))

4.      assuming the ring numbers start in B2 and the equation is entered in C3 - the above says, if either B2=B3 or B3=B4 i.e. the number to the left (B3) is the same as the number above it or below it, then the statement (C3) is TRUE otherwise it is FALSE. This finds both duplicated (or the three triplicate etc.) numbers.

5.      Copy the cell (C3) all the way down the sheet to the bottom of the data.

6.      Highlight the C column now containing TRUE or FALSE.

7.      Apply Data/Filter/Autofilter.

8.      Click the pulldown and choose TRUE.

9.      Cracked it.

John's real problem is working out which of the duplicates was entered incorrectly. Funnily enough, I was doing almost the same thing as John on 8,000 bird rings a few days ago.

Frank Linley, Woolston Eyes Bird Reserve, Cheshire

In Excel there is an easy solution - use formula =COUNTIF(A:A,A1) in B1 where the ring numbers run down column A. Copy it down column B. Where there is a duplicate the result is more than 1.

To highlight the effect more in a long list, use an IF statement around it e.g. =IF(COUNTIF(A:A,A1)>1,"Duplicate",""). Delete the duplicate lines you find and the matching items will stop showing the text automatically! You can even use Find to search for the word Duplicate rather than do it manually. Put the finding and the line deleting in a simple looping macro and you can wipe out the duplicates in no time.
Andrew Kinnon, Glenrothes, Fife

Firstly sort the spreadsheet by the ring number column and then insert a column into the worksheet, ideally adjacent to this column. Assuming the ring number column is column "C" and the new column is column "D", enter the formula =IF(C1=C2,"duplicate","") into cell D2.   Copy the cell D2 to all the remaining cells in column D.

The result will display the word "duplicate" next to the second and subsequent duplicated ring numbers in an otherwise blank column. This makes finding the duplicated ring numbers simple.

The IF statement simply compares the previous row's cell value with the current row's cell value in column C.  By copying the cell to the remaining cells in column D, Excel changes the cell references within the formula to reflect the formula's position in column D so the formula becomes
=IF(C2=C3,"dup",""), =IF(C3=C4,"dup","") etc. as you move down the column.
Duncan Wang,

A simple solution would be to use Excel's conditional formatting in conjunction with the method he already uses. After sorting the ring number column (say, column B with data starting at line 4), simply go to cell B5 and invoke conditional formatting from the format menu. Set the condition so that 'Cell Value Is equal to' and enter "=B4" (without the quotation marks) in the next box. Then set the format using bold and bright colour(s), and click OK. Finally, use the format painter to copy the format of cell B5 to all the other data cells in column B.

Any duplicate ring numbers will then stand out very clearly from the others, making them much easier to spot.
Vivian Dunn,

TV VIEWERS

Can you tell me how they determine the number of viewers looking at a particular television programme, and how can they tell how many switched off when the programme changes? These figures are regularly given in the press but I cannot see how they are arrived at.

Robin Morris, via email

We have a viewing monitor in our house. It was installed by RSMB Television Research (www.rsmb.co.uk) and there is a handset with the names of each of our family on it (we provide details of name, age, gender, etc) and we record who is watching each programme and if we leave the room.  There is also the ability to record if visitors are watching programmes.

The information is collected in the early hours of each morning via the telephone line. Each year we receive an amount towards our TV licence and each member receives a £5 gift voucher of their choice every six months.
Janet Fuller
 

HOME HELP

My family want to build our own house.  We have plenty of ideas, but no means of expressing them except for scribbly drawings.  We do not want to use an architect as we feel we do not need assistance in design. We do however need drawings and measurements.  We intend to use a quantity surveyor/buildings surveyor to comply with building regulations and apply for permission.  Is there any software available for DIY house (and garden) design?

Paul Reynolds, via email

 
I have built three properties. I knew what I wanted for the first, but had to use an architect - it was a condition of the plot purchase. I let him show me what he proposed - he had drawn detained plans for the sort of house I could buy already built for my self-build budget. He had done a lot of detail work without first asking me whether the basic proposal was ok.

So I them told him what I wanted, and he got his own back by billing me for a project that would have cost twice what I could afford, after the foundations had been constructed, so I couldn't cut it down a bit.

He refused to supervise, because he said he had no control where the client was also the builder. But that was 25 years ago. I don't remember having difficulty getting a mortgage.

The next, I used a cheaper, part-qualified fellow who had been recommended to me. We got on quite well because we discussed what I wanted. I built the house for sale. It cost £21,000 and I sold it for £19,000. (Recently advertised for £190,000.) I went back to electronics!

And recently, I built a bungalow. I drew my own plans - in pencil on tracing paper over a squared backing. The planning officer said he couldn't reject my drawings because he didn't like them, but he didn't like them, so rejected my plans anyway! He said that presentation could make all the difference. So I got a young qualified architect to draw up my plans. (Ask if your local college has someone capable and cheap.) Anyway, the council then found new reasons to reject my proposals.

The young architect did drawings for building regulations approval, but I did the planning applications. After six applications and two appeals to the DTI got approval.

The main thing is - keep it simple. I had an L shaped bungalow, and only after I had finished did a friend point out that if I had lost a couple of feet off one bedroom, I could have made the garage detached, so that I would have had two rectangles - no inside corners for the brickwork and no roof valleys. I have three steel beams - I wouldn't have needed any of those if I had turned the garage roof 90 degrees. I reckon that I could have saved three months and four thousand pounds. Several professionals looked at my drawings before I started work. None of them suggested that it could be simplified.

As for material costs - shop around. Go where the trade go. Use trade tools.Demand trade discounts. One of the dealers will break ranks, and when you have one trade card, you can use that to get others. I did most of the work on my bungalow. It took me twice as long as it would take a professional, but I didn't charge me much. All good fun - good luck!!
S. Banks,

MOUSELESS TEXT READER

Can anyone suggest the cheapest scanner and software that will get text from magazines and newspapers, put it into a Word document and then read it out without any proof reading? It is vital that all this be done without any use of the mouse, as our clients are blind. Obviously, it is also impossible to use the mouse even to select text. RNIB computer volunteers have been trying to crack this for months. We have ReadBack, Readplease and we have used Omnipage and Textbridge as well as various macros, but always, we get to the dreaded point where the mouse has to be used.
Brian Rhodes, via email

I am also currently working with a totally blind client (for a charity called SCAMP, the Swindon Communication project) and we use a program called Supernova. (www.dolphinuk.co.uk or info@dolphinuk.co.uk).  The program works alongside a couple of other programs - Synthesiser Access Manager (or SAM) and Orpheus.  Supernova is extremely flexible and configurable - you can even 'teach' it to recognise individual bitmaps and recite an appropriate title!  No mouse input is required at all, though the downside is that there are seemingly 1001 keyboard strokes to memorise, though a couple of dozen of these are the crucial ones.  It also comes with a screen magnifier for partially sighted users.
Peter W Allison,

CAN YOU HELP?

Next year I shall be getting married (that's not the problem BTW) and I am wanting to stream the wedding over the net as I have a lot of family in Canada who won't be able to make it, now what I am trying to track down is a wearable (i.e. very small) remote web cam, I've tried various spy gadget sites but with no luck. Does anyone know of such a thing?
Adam Davies, Shrewsbury, Shropshire,

From time to time we do tickets for fundraising events like Barn Dances, and we wondered if there is any way of numbering each ticket?  Numbering whole pages is easy, but we do six tickets to each page and cut them up
afterwards. Any ideas?
Reg and Margaret Brown, via email

I'm seeking to input old black & white 8mm cine film directly into a PC (rather than the indirect method of projection and filming with a camcorder) for the purpose of editing etc. Some scanners can take 35mm film, but has anyone had any experience with 8mm movie film?

Bryan Bedwell, via email