Calculating a minimum payment figure using a formula or function

Calculating a minimum payment figure using a formula or function

Hello, I'm hoping someone may be able to help me with something that probably seems quite obvious or basic however, my knowledge on formulas or functions within Smartsheet is not advanced, so I apologise in advance!

I'm trying to get my finances in order and thought I'd use Smartsheet to plan this.

I've done a screen shot of the columns as below:

I'm trying to figure out the best way of having either a formula or function calculate automatically for me the figure in the "Totals (per month / per annum). I was wondering if it would be through using SUM?

I want it to calculate the minimum payment of "Total Amount Outstanding" using the figures that are in the "Minimum Payment" column, without having to change it to a decimal.

Any advice you can provide would be great.

Many thanks

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    The only difference it makes is in the solution. Try something like this to get your minimum payment...


    =[Total Amount Outstanding]@row * (VALUE(SUBSTITUTE([Minimum Payment]@row, "%", "")) / 100)


    Basically we use the SUBSTITUTE function to remove the %, the VALUE function to convert the rest into a numerical value, divide it by 100 to create the appropriate decimal, then finally multiply it by the [Total Amount Outstanding] to calculate your minimum payment.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    It sounds like it is set as a percentage format. This should work for you a little more efficiently.

    =[Total Amount Outstanding]@row * [Minimum Payment]@row


    Here's a little tip... Any time you divide by 1, you can just remove it entirely. Any number divided by 1 is that number, so

    12 / 1 = 12

    1563416874643 / 1 = 1563416874643

    VALUE(SUBSTITUTE([Minimum Payment]@row, "%", "")) / 1 = VALUE(SUBSTITUTE([Minimum Payment]@row, "%", ""))


    May save you a few keystrokes in the future.

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Is the Minimum Payment column formatted as a percentage where you simply enter a number and it converts to a percentage, or are you manually entering the %?

  • I'm just manually entering this at the moment - does this make a difference?

  • Hi Paul

    That's really good of you to look into that for me thanks!

    It has worked but not fully given me the correct answer - it calculated the figure as £0.10, so I changed the formula as below:

    =[Total Amount Outstanding]@row * (VALUE(SUBSTITUTE([Minimum Payment]@row, "%", "")) / 1)

    And this provided the result.

    Thanks so much for taking the time to help me :-)

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It almost seems as if it is registering your [Minimum Payment] column as a decimal already. Are you actually typing in the "%" symbol when you put a number in that cell, or are you just keying a number and the "%" is popping up on its own?

  • When I typed it in after the number, I think it has then recognised this and it applies the format.

Sign In or Register to comment.