OVER 2 YOU, 040 (26/07/01)

SOLAR POWER

I am very interested in installing solar powered heating and possibly electrical systems in my home. Can it be done in our climate? Are there any web sites that can help me to figure out the cost and practicalities?

Norman Stevens, via email

The Centre for Alternative Technology site www.cat.org.uk has a links page, which lists quite a few references to solar power. For my money, BP notwithstanding, solar electricity will rarely be worth it in the UK, either environmentally or economically, for a good few years yet. Solar heating seems to be either amateur or overpriced.
David Pollard,

For Solar Power heating etc. an excellent site with a free downloadable bi-monthly magazine, past articles etc., in .pdf format is www.homepower.com

There are also newsgroups such as alt.solar.photovoltaic and alt.energy.homepower

Martin Bennetts,

There is currently a series in the magazine Electronics & Beyond at: www.electronicsandbeyond.com that covers some points and suppliers (this feature started in last months issue). Also J. Bull Electrical at: www.bullnet.co.uk/shops/test/contents.htm, has a good collection of data
sheets, bits and pieces etc.
Peter Gee, Dartford, Kent

 
Solar electricity systems can and are being used in the UK visit any of our
UK distributor sites:
Wind and Sun http://www.windandsun.co.uk
Beco Solar http://www.becosolar.com
Marlec  http://www.marlec.co.uk

Mark Hammonds, BP Solar http://www.bpsolar.com
Solar Education site http://www.solarpartners.org

 
MAC FILES

I have a problem transferring publications produced using PageMaker on a Mac to PageMaker on a PC. I had hoped to achieve this by sending them from the Mac to the PC as e-mail attachments but PageMaker on the PC will not open them. When I try I get an error message saying 'This file format cannot be opened using this application'. The files have been saved on a CD-ROM in the Mac but of course the PC cannot 'see', let alone read them.
Ian Penfold, via email

I believe there may be two issues here. Let me deal with the first that of the format the data has been applied by the Mac to the CD-ROM. This should ideally be in ISO 9660. If the Operating System is prior to Windows 95/98 then this should be native 8.3; that is the name should be comprised of no more than eight characters followed by a dot and a three-letter extension. There should be no spaces or punctuation marks within the eight alphanumeric characters, and the three-letter extension should be the recognised abbreviation for the application required by the operating system to open the document.

If the operating system is later than this on a PC, then the Joliet convention allowing for longer filenames can apply in which case the name itself can be longer. Since the Mac is limited more realistically to 27 characters (which would include the dot extension) Ian can use a Mac name that is limited to 23 characters.

Now to the extension itself. In the case of PageMaker this is either P65 or T65. So the name for safety might be 'MyMagzne.P65' or 'My Magazine.P65' according to circumstance where the actual name be substituted for my generalisation.

Lastly, when sending the file by email as an attachment, several other possibilities can introduce yet more problems. These range from the fact that files on Mac come in two parts; a data fork and a resource fork, which confuses PCs into thinking there are two files, each with the same name! A way round this is to use a compression format such as Stuffit - most newer PCs are able to use WinZip to decompress these files. There are also less well-known packages that strip the resource fork from the file before transmission.
Rod Wynne-Powell, solphoto@dircon.co.uk

Ian Penfold may find his PageMaker Mac to PC problem solved if he visits Adobe's support site. He should go to www.adobe.com/support/techdocs/c9f2.htm where he can read several screens full of conversion options. Adobe has an extensive Support Knowledge base covering all their products.
Roger Trigg,

As an operative used to working in both Mac OS and PC Windows/NT may I suggest two possibilities for Ian Penfold's problem. Firstly, in the case of saving them to CD, he should end with the suffix .pm4, 5, 6 or the number PM version he uses. Secondly, if this is not convenient, he should try opening the files within the program and not just double clicking on it.
Terry Verney,

COUNTIF FUNCTIONS

I am using an Excel spreadsheet to track items and the countif function to use letters to signify different items. However, I cannot find a way to differentiate between upper and lower case letters.  I wish to use lower case to count the value of a half and upper case the value of one. Can this be done?  I have already used the help function to review the entire function list, but to no avail.
David Howarth, via email

The solution is to use one of the case conversion functions [=UPPER(), =Proper(), or =lower()] in an adjacent column.  Then embed this within an =Exact() function comparing the unconverted text with the converted version.  Alternatively place the exact function in another adjacent column if this aids understanding

David Bennett David Bennett

The following formula should solve David Howarth's problem. It will register 0.5 for lower case letters and 1.00 for upper case letters in a particular cell. Any other character will be returned as zero. In the example the letter in question is in cell C6.

 =IF(AND(CODE(C6)>=97,CODE(C6)<=122),0.5,IF

(AND(CODE(C6)>=65,CODE(C6)<=90),1,0))

The formula can be copied down in, for instance, a column, thus allowing it to refer to a column of letters. A Function could also be created in VB to do this.

Phil Andrews,

One solution for your problem is to use the CODE function to find the first letter in your word this will then return a number between 65 and 90 for Capitals and 97 and 122 for lower case. You can use an IF function then to determine which group you are in. Be careful not to have a space in front of your word otherwise you will get the character number 32. You can avoid this problem by using the function TRIM to remove leading blanks.

If you do not want to inspect the first word it gets more complicated and you will have to use FIND to select your word and then inspect it using the idea above. If you need to select a surname from a list then it gets more complicated as people often type John Brown, in this case you FIND the blank space and proceed. 

John Hooton,

COUNTIF and other functions that use comparisons do not distinguish between upper and lower case. As an example say that column A contains 11 A's and 9 a's in no particular order with a title in row 1.You have probably already tried something along the lines of the following formulas =COUNTIF(A:A,"A") or =COUNTIF(A:A,"a") which results in an answer of 20 in both cases.  The result that you would like to get is 11 or 4.5 respectively for a total of 15.5.

In order to accomplish your task you'll need to combine several of the functions, which you may have come across in your examination of help. One solution is to create an array formula using EXACT, IF and SUM. The EXACT function allows you to compare two strings and is case sensitive. If the strings are the same, EXACT returns TRUE otherwise FALSE.  By using the EXACT function for the logical test of an IF function and putting a value of 1 (or .5) in the 'value if true' position and a 0 in the 'value if false' position you can set up a counter.  To count the A's the IF function looks like...=IF(EXACT(A2:A21,"A"),1,0). 

For the a's replace the "A" with "a" and the 1 with a .5.  To complete the counter the expression needs to be placed into the SUM function and turned into an array formula.  Step one will look like...=SUM(IF(EXACT(A2:A21,"A"),1,0)).  To turn the formula into an array formula, select the cell containing the formula again and use the keys CTRL+SHIFT+ENTER.  This will put a set of {} around the formula making it look like...{=SUM(IF(EXACT(A2:A21,"A"),1,0))}.  You can nest if statements and develop a single formula to sum the A's and a's using
only one cell.  The array formula to do that is... {=SUM(IF(EXACT(A2:A21,"A"),1,IF

(EXACT(A2:A21,"a"),0.5,0)))}.  The second IF function is placed in the location of the 'value if false' of the first IF function.  Hopefully, you now have the results you expect. I rest my case (or is that CASE),
Marcus Weber

REMOVING RETURNS

I often receive information in plain text format and would like to put it into Word documents or a web page. Usually this results in a boring session clicking the end of each line and pressing delete to "join up" all the text by getting rid of "hard" carriage returns etc. Does anyone know of a utility, which will do this automatically?  Usually it's not too long a job but I just got a text document that is a megabyte long!
Richard Limebear, via email

I think carriage returns can be removed rather more easily than has been so far suggested. I open offending files in WordPad and then immediately save them using one of the 'save as text' options. This removes all the formatting as well as the hard carriage returns and gives a nice clean file to use in an application, such as a DTP programme. It's also quite a good way of quickly sorting out emails received in HTML format, should you want to use them in another document.

Granville Davies,

CAN YOU HELP?

I organise our annual village fun run which now attracts over 500 runners. I record the runner's number and details on an Excel spreadsheet. Does anybody know of a way of capturing the time each runner takes by entering the runner's
number as they cross the finish line and then producing a results table for different ages/genders?
Steve Spillane, via email

I would like to write a macro that will insert the current date into a cell of an Excel spreadsheet such that it does not update. The today() function cannot be used as this will update automatically. The problem would be simple to resolve in Word but, so far, I cannot find an answer in Excel. Surely there must be away of doing it - any ideas please?
Vivian Dunn, via e-mail

All of our early Parish Council records were compiled on an Amstrad 9512plus and saved to 5¼ inch floppies.  Being a very small council, we cannot afford the cost of having the hundreds of disks transcribed professionally but could do this ourselves if we could get hold of the appropriate software.  We are fortunate in that the Amstrad disks are 5¼ inch and will thus fit into the floppy drive on our PC 
John Stevenson, via e-mail

Do you know of a simple program that I can use to coordinate my PC (Office 2000) and scanner so that I can collate the feedback from surveys that I send out? The questions I pose are followed by 3 boxes into which the recipient is asked to place a tick in his/her preferred choice. How can my machine count how many said A, B or C, and how many of each?

Simon Stewart, via e-mail

Forty of us 'wrinklies' are in a Lottery Syndicate. We have each selected an unvaried line of 6 numbers. Is there a programme which would enable lines that come up with 3 (or more) winning numbers to be identified on input of the weekly draw results?

Richard Hind, via e-mail