Excel (2007) formula help list of numbers

  tonyq 06:45 08 Aug 2012

Please can you good people tell me what formula I need for the following,using Excel 2007. I need to find 5/6 of a list of numbers in one column,and 5/12 of the list in another column. The results need to be rounded up to a full number if the result is .5 or above,or rounded down if below .5 e.g 5/6 of 50 5/12 of 50

  Woolwell 10:56 08 Aug 2012

Possibly the easiest way is to enter in column b1 =A15/6 and copy down and in column c1 =A15/12 and copy the formula down. Then for both columns B and C on the ribbon on the home tab and in the number section decrease the decimal to zero.

  stlucia2 19:43 08 Aug 2012

The solution Woolwell has given should be b1 = A15/6 and c1 = A15/12 I believe, and is usually satisfactory. BUT, if you then want to add all your rounded numbers you'll probably find the answer is not exactly the sum of what's displayed because Excel uses the original numbers, rather than the rounded ones, when carrying out any further calculation.

If you want further calculations (adding, multiplying, etc.) to use the rounded numbers you see on the screen, instead of the original unrounded numbers, you need to use the ROUND function. So b1 would be =ROUND(A1*5/12,0), where the ,0 tells it to round to zero decimal places.

  stlucia2 19:48 08 Aug 2012

Oh dear, I've got the same problem as Woolwell -- this forum seems to ignore the * symbol that should go between the "A1" and "5/6" in the formula :-(

Let's see if it will display properly if I put spaces in ... b1 = a1 * 5 / 6 and c1 = a1 * 5 / 12. Omit the spaces when inputting into Excel.

  Woolwell 22:27 08 Aug 2012

stlucia2 - thanks for spotting the missing *

  Woolwell 22:30 08 Aug 2012

The addition depends on how you want to add up. Round could give you an incorrect total.

  Housten 12:31 09 Aug 2012

OK! I use Office 2003, which does everything I want!!

I think the simplest way to round to the nearest whole number is by using int((your calculation)+.5). This way '(your calculation)' works out what you want, then the rest of the calculation adds 0.5 to it, so that if the number is more than 0.500 then it increases above the next whole number, if less it doesn't reach the next number. The 'int' converts it in to an integer - i.e. a whole number - and you then get everything you want. I have used this for many years and have yet to find it wanting.

  Woolwell 13:25 09 Aug 2012

Housten that doesn't do what tonyq wants. If below .5 he wants it rounded down and if above .5 rounded up which the correct mathematical way of rounding. Your system always rounds up.

  tonyq 16:30 09 Aug 2012

Thank you all for the replies. After playing around with all your suggestions,I have ended up with the following formula that stlucia2 Posted Yesterday at 7:43PM which seems to work,which is =ROUND(A1*5/12,0). Thank you all for your help.

  Housten 16:13 12 Aug 2012


I don't want to be picky, but you are wrong!! Take 9.50, as an example. if 0.5 is added to it the answer becomes 10.0, which when integerd becomes 10. Now take 9.49, add 0.50, and you get 9.99, when this is integered you get 9!

I think that takes care of your disagreement as to whether or not my suggestion works. I have used this at work and at home for almost the last 30 years, so if there had been a problem with it, I think I would have spotted it by now.

  Woolwell 16:42 12 Aug 2012

Housten that is because integer rounds down. With the risk of hi-jacking this thread if the 2 numbers are 9.25 and 9.15 then the rounding gives 9 and your method also gives 9. Added together they come 18.4 and the rounding is 18. If you use your method and add before the integer is applied then the total is 19.4 and the rounding/integer is 19. You are increasing the mathematical risk.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

HomePod review

Les meilleurs logiciels de montage vidéo gratuits (2018)