I'm getting incorrect calculations for this simple formula.
I'm trying to calculate the "Net Balance" with this simple formula but I'm getting incorrect calculations.
Current formula: =[Gross Amount]@row - (SUM([Deposit Amount]@row, [Credit Amount]@row, [Discount Amount]@row))
I have tried using VALUE and SUM too but with that I get incorrect operation error. Can someone please help me out with this.
Thank you!
Answers
-
Hmm, I was able to get your formula to work on a test sheet. The only thing I can think of is that it's somehow thrown off by the formula you have in your discount amount column. Are you able to share that formula?
-
Yes, the "Discount Amount" formula is
=[Gross Amount]@row - ([Gross Amount]@row * VALUE([Discount % Applied]@row))
-
The formula you are using seems to be missing some brackets and possibly has a syntax error. For Smartsheet, it's important to ensure proper use of parentheses for functions and to reference cells correctly.
The correct formula for calculating "Net Balance" considering that you want to subtract 'Deposit Amount', 'Credit Amount', and 'Discount Amount' from 'Gross Amount' for each row should look like this:
=[Gross Amount]@row - [Deposit Amount]@row - [Credit Amount]@row - [Discount Amount]@row
If you want to ensure that the values are treated as numbers, you can wrap each cell reference with the
VALUE()
function, but it is not usually necessary unless the cell is formatted as text.For summing up values, you can simply add the values as shown in the formula above. The
SUM()
function is used when you want to add up a range of cells or multiple ranges, and it's not necessary when you are subtracting individual cell values from one another. -
I figured out the issue. Thank you'll.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!