Calculating a minimum payment figure using a formula or function

SteCoxy
SteCoxy ✭✭✭✭✭✭

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 Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul 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 %?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • SteCoxy
    SteCoxy ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • SteCoxy
    SteCoxy ✭✭✭✭✭✭

    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 Newcome
    Paul 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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • SteCoxy
    SteCoxy ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!