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!