# Calculating a minimum payment figure using a formula or function

Options
✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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 %?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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 :-)

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!