| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 29321 | 2003-01-16 22:17:00 | excel xp rounding | wotz (335) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 114453 | 2003-01-16 22:17:00 | I want to do some calculations that round to the nearest .5. I have found the function =round(calculation,1) which rounds to the nearest .1 but can't see anything to go to the nearest .5 Another option could be to multiply the calculation by 10, round to nearest 5 then divide by 10, but can't make that work either. Any takers? |
wotz (335) | ||
| 114454 | 2003-01-16 22:45:00 | The function CEILING may work for you. If cells in Column B contain the pre-rounded numbers and if the cells in Column C are to contain the rounded numbers then drag and paste the CEILING formula into the cell references of Column C beginning like this = =ceiling(B1, 0.05) and so on for the rest of the cells down column C. The drag and paste function in Excel will ensure the relative cell references are used of course. Hope this helps ! |
blank_harry (1661) | ||
| 114455 | 2003-01-16 22:45:00 | You could try it with an if statement. | mikebartnz (21) | ||
| 114456 | 2003-01-16 23:10:00 | Thanks Harry. Thats exactly what I wanted | wotz (335) | ||
| 114457 | 2003-01-17 00:38:00 | Harry did give a helpful response and Wotz was happy, so maybe I shouldn't say anything. However, the CEILING() function doesn't actually answer the question as asked. Take the number 3.51 Then =CEILING(3.51,.5) gives the result 4, not the 3.5 which is the correct rounding "to the nearest .5". CEILING rounds to the next highest .5 In similar circumstances I personally would prefer =ROUND(2*3.51,0)/2 which does round the number to the nearest .5 There's other ways of doing it too. Also the interesting question of how to display or format (in the same cell) the number to the nearest .5 without changing the number itself. |
rugila (214) | ||
| 114458 | 2003-01-17 01:44:00 | You are correct rugila, however rounding up is what I wanted. 'Nearest .5' was a mistake in my original post. | wotz (335) | ||
| 114459 | 2003-01-17 02:58:00 | Rugila, you've made an excellent point, I stand corrected. I've had another look at the issue of rounding and by jove I forgot about the old Swedish approach to maths - commonly encountered at the checkout of you favourite supermarket. Now, even though wotz's indicated preference lies with rounding up to the nearest five, there is a another Excel function that employs the accuracy that your view, Rugila, entails. It is MROUND. If the function MROUND were used along the lines of =mround(B1, 0.05) for a given number of goods costing $1.40, $1.41, $1.42, $1.43, $1.44 and $1.45, then MROUND will make those goods $1.40, $1.40, $1.40, $1.45, $1.45 and $1.45 respectively. Which is what occurs when we're at the checkout of our favourite supermarket. Although I'd prefer ROUNDDOWN myself. |
blank_harry (1661) | ||
| 1 | |||||