I'm getting incorrect calculations for this simple formula.

santmitbhamrah

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!

brianschmidt

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?

santmitbhamrah

Yes, the "Discount Amount" formula is

=[Gross Amount]@row - ([Gross Amount]@row * VALUE([Discount % Applied]@row))

David Jasven

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.

santmitbhamrah

I figured out the issue. Thank you'll.