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

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!

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!
Answers

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!

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

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!

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

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!

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

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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!