Rounding up the figures to the nearest integer RRS feed

  • Question

  • Hi

    I'm working with a calculated field that needs to take a number and divide it by 8 for me to plan how many sessions I'd need for a project. This part is working perfectly. The formula below works where it rounds to the nearest integer but I need it to round UP to the nearest integer. How could I do that?


    Thank you


    Wednesday, September 27, 2017 12:02 PM

All replies

  • Hi Wendy,

    You can refer to the following post to round up value :

    You could also take the left characters (left function) before the comma and add +1.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller

    Wednesday, September 27, 2017 12:21 PM
  • IIf([Number1] Mod 8=0,[Number1]/8,IIf([Number1] Mod 8>3,format([Number1]/8,"0"),format([Number1]/8,"0")+1))

    Ben Howard [MVP] | web | blog | book

    Wednesday, September 27, 2017 12:53 PM
  • Wendy,

    You can visit the Pulse article (thanks to Guillaume for mentioning it) to read the details or simply use the formula Abs( Int( -( [Number1] / 8 ) ) ) in a custom text field.

    Ben's formula works perfectly in MS Excel (or VBA), but MS Project's Mod operation is different since the operands are rounded off to the nearest even integer before the calculation. In order to test, for example, enter 3.55 or 15.78 to the Number1 field, the formula will not return the results expected.

    Ismet Kocaman | eBook on Formulas

    • Edited by Ismet Kocaman Thursday, September 28, 2017 11:33 PM typo
    Thursday, September 28, 2017 10:43 PM