Alan Medic Posted October 7, 2011 Share Posted October 7, 2011 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. Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/ Share on other sites More sharing options...
maxxi Posted October 7, 2011 Share Posted October 7, 2011 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? Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-480725 Share on other sites More sharing options...
smallblue Posted October 7, 2011 Share Posted October 7, 2011 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. Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-480728 Share on other sites More sharing options...
Alan Medic Posted October 7, 2011 Author Share Posted October 7, 2011 Thank you smallblue and maxxi for the advice. Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-480735 Share on other sites More sharing options...
Senor Chevalier Posted October 8, 2011 Share Posted October 8, 2011 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 Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-480844 Share on other sites More sharing options...
maxxi Posted October 8, 2011 Share Posted October 8, 2011 *snigger* you said 'rooting' Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-486811 Share on other sites More sharing options...
Senor Chevalier Posted October 8, 2011 Share Posted October 8, 2011 Suspected I might not get away with that... Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-489650 Share on other sites More sharing options...
Alan Medic Posted November 11, 2013 Author Share Posted November 11, 2013 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=C1However 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. Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-695278 Share on other sites More sharing options...
Loz Posted November 11, 2013 Share Posted November 11, 2013 Use 'Paste Special' and then select 'Values'. You only get the value of each cell, rather than the formulae behind them. Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-695280 Share on other sites More sharing options...
Alan Medic Posted November 11, 2013 Author Share Posted November 11, 2013 Ah thank you Loz. It's easy when you know how. It's hard work when you don't. Link to comment https://www.eastdulwichforum.co.uk/topic/19940-excel-expert-please/#findComment-695288 Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now