Calculating a minimum payment figure using a formula or function

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

Paul Newcome

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

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.

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

SteCoxy

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

Paul Newcome

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.

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

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?

SteCoxy

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

Paul Newcome

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.