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.

  • 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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!