Forum Discussion

amrsalah's avatar
amrsalah
Copper Contributor
Jun 17, 2021

Cumulative roundup

Hi All,

I have a case that its calculation is a cumulative after rounding the number, for example; I have starting number (e.g. 7000) this number is growing each time by 15% taking in consideration to round it up to the nearest multiply of 100, so the result would be 8100 (7000*1.15=8050 after rounding up it will be 8100), then then next time the calculation should take place on the 8100 not the 8050 (8100*1.15 = 9315 should be rounded to 9400, and so on. How can I do this?

 

Thanks,
Amr Salah, CM.

excel
Formulas and Functions

6 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Jun 17, 2021
    If A1 = 7000, then try this in B1 and copy down:

    =ROUNDUP(A1*115%,-2)
    • amrsalah's avatar
      amrsalah
      Copper Contributor
      Jun 23, 2021
      Thank you.
      But what I need is to be updated automatically in the same cell according to a variable in the formula indicated the number of the instance, for example if this instance 1 so the result is 7000, if 2 the result should be 8100, and so on...
      • JMB17's avatar
        JMB17
        Bronze Contributor
        Jun 23, 2021

        amrsalah 

         

        Another option would be to use a custom vba function.

         

        Function MyRoundup(base As Double, multiplier As Double, numDigits As Integer, index As Integer) As Variant
     Dim res As Double, i As Long
     
     If index <= 0 Then
          MyRoundup = CVErr(xlErrValue)
          Exit Function
     End If
     
     res = base
     
     For i = 1 To index - 1
          res = Application.RoundUp(res * multiplier, numDigits)
     Next i
     
     MyRoundup = res
     
End Function

         

         

        To use the function, you would  copy/paste into a standard code module. Then, to find the 5th element, you would enter enter: 

        =myroundup(7000,115%,-2,5)

         

Resources