Basic Subtraction

ahw47

I want to track payments and outstanding balances for clients. I want to to calculate the Remaining Balance by subtracting the Amount Invoiced from the Amount Paid.

This is the formula I'm using: =[Amount Invoiced]@row - [Amount Paid]@row

and I get nothing but #invalidoperation. Am I missing something for this seemingly basic formula?

Georgie

Hi @ahw47 ,

Thanks for providing the screenshot. I was able to reproduce this issue in Table View when my values in the amounts columns were typed with the dollar sign into cells, eg “$4,000.00”.

I’d recommend taking the following steps to troubleshoot this in your sheet:

• Double click a cell in the Amount Paid or Amount Invoiced column - only numbers should appear with no text or symbols
• Test by using =VALUE() on ONE cell reference and see if the error is still presented. That is, try the following formulas one after the other:
  • =VALUE([Amount Invoiced]@row ) - [Amount Paid]@row  
  • =[Amount Invoiced]@row - VALUE([Amount Paid]@row )

If the $ sign shows when double-clicking a cell, that means the value is seen as text, which is an invalid value to add or subtract.

I’d recommend formatting the Amount Paid and Amount Invoiced columns to the desired currency (see here for more information) and editing the values within the cells to ensure that only numbers are input into the cells, and then your formula should work as expected.

Hope that helps!

Georgie

Darren Mullen

@ahw47 It's possible one or both of your columns are storing the numbers as text. Wrap the column references in the VALUE() function to ensure you are operating on numbers vs text.

This especially is plausible if you are importing the data from another system or copy and pasting it in.

 =value([Amount Invoiced]@row) - value([Amount Paid]@row)

Georgie

Hi @ahw47,

Are you still looking for help with this? If so, could you let us know what you see when you try @Darren Mullen’s suggested formula? Do you get an error message or an incorrect value, for example?

If you could also share some screenshots of your sheet that should help us to understand what might be causing the issue.

Thanks,

Georgie

ahw47

I correct the formula to : =VALUE([Amount Paid]@row ) - VALUE([Amount Invoiced]@row ) and I'm getting the result #INVALID VALUE

Now, with the data that I have, I know that many of the remaining balance values will add up to $0.00. Is that a potential factor? I am getting the same result regardless of the expected sum

Georgie

Hi @ahw47,

That’s strange. I tested this on my end and I’m unable to reproduce the error you’re seeing. 

Would you be able to share screenshots of your sheet (you could make a copy of the sheet with dummy data if you can’t show us the actual sheet) showing rows that contain the formula and are showing the error and also showing the column properties for the Amount Paid and Amount Invoiced columns as well as the column that contains the formula. That will allow me to test with the same setup as you and should help us find the cause.

Thanks,

Georgie

ahw47

Georgie

Hi @ahw47 ,

Thanks for providing the screenshot. I was able to reproduce this issue in Table View when my values in the amounts columns were typed with the dollar sign into cells, eg “$4,000.00”.

I’d recommend taking the following steps to troubleshoot this in your sheet:

• Double click a cell in the Amount Paid or Amount Invoiced column - only numbers should appear with no text or symbols
• Test by using =VALUE() on ONE cell reference and see if the error is still presented. That is, try the following formulas one after the other:
  • =VALUE([Amount Invoiced]@row ) - [Amount Paid]@row  
  • =[Amount Invoiced]@row - VALUE([Amount Paid]@row )

If the $ sign shows when double-clicking a cell, that means the value is seen as text, which is an invalid value to add or subtract.

I’d recommend formatting the Amount Paid and Amount Invoiced columns to the desired currency (see here for more information) and editing the values within the cells to ensure that only numbers are input into the cells, and then your formula should work as expected.

Hope that helps!

Georgie

ahw47

That worked! thank you!