Excel Expert please

[Alan Medic]

I'm conducting a little family game (in various countries)based on guessing the scores of each team in the RWC this w/end.

Points are accumulated from the distance your guess is to the actual score. I'm trying to put this on a spreadsheet for simplicity. However if I use a simple calculation of deducting the actual less the guessed score I'm likely to get a plus or minus figure eg Wales score 10 points. One guess is 15, their score is 5. Another guess is 7 so their score is 3. However using the same calculation for the first one the result would be -3.

Is there a way to make all answers positive regardless of the figure being above or below a certain number?

Don't go to a lot of trouble if there isn't a simple answer. Thanks.

[maxxi]

I know it's Friday but shouldn't you wait until teacher says 'Chairs on desks and don't forget dinner money on Monday!' before such dangerous skylarking?

[smallblue]

Hi,

You need the absolute number function (absolute numbers return a positive value regardless of sign).

=abs(A9) for returning a positive value of cell A9 for example. Modify as appropriate.

[Alan Medic]

Thank you smallblue and maxxi for the advice.

[Senor Chevalier]

Or if you don't know or can't remember the functions you can use first principles and go for "squaring" to give a positive number and then "rooting" to return the positive square root.

=(A9^2)^0.5

[maxxi]

*snigger* you said 'rooting'

[Senor Chevalier]

Suspected I might not get away with that...

[Alan Medic]

Another small problem.I'd be grateful for help.

I cut and pasted two columns of figures from the company software to a spreadsheet. In a third column I want to compare the first two columns for differences or not as the case may be.

I can do this if I'm just using figures which weren't imported as in A1-B1=C1

However something is stopping that happening which I'm sure is due to the importation in that there is something in the cells other than the figures, which doesn't allow the normal formula to work.

Can someone explain what I need to do to purge the cells of whatever it is and leave me just with the imported figures. I could spend hours figuring it out but I need to do something in a hurry.

Thanks.

[Loz]

Use 'Paste Special' and then select 'Values'. You only get the value of each cell, rather than the formulae behind them.

[Alan Medic]

Ah thank you Loz. It's easy when you know how. It's hard work when you don't.