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 ...
excel
Formulas and Functions
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)

       

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 23, 2021

      amrsalah 

      No idea what your setup is so you might have to adapt

       

       

      in B2 and copy down

      =ROUNDUP( IF(ISTEXT(B1),A2,B1) * 1.15, -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...

Resources