Google Sheets – Reverse Subtraction if Less Than Zero

formulasgoogle sheetsgoogle-sheets-arrayformula

I have a simple Google spreadsheet that Takes the estimated cost for a service provided and compares it to the actual cost of the service. the equation I have in the Q column is =K4-P4. When the estimated cost (column K) is less than the Actual cost (column P) I need a positive number in Column Q. And I need the reverse of that if K is greater than p. I'm new to spreadsheets and would appreciate any help.

Best Answer

=ARRAYFORMULA(IF(LEN(K4:K&P4:P), IF(K4:K-P4:P<0, P4:P-K4:K, K4:K-P4:P), ))

Related Solutions

Google Sheets – Sum of Two Numbers with a Minimum of Zero

You can use the MAX function for this, since

MAX( A3+B3, 0 )

will return 0, when the sum is negative.

Google-sheets – How to zero out cell if filtered sum is less than 0

For your first question, I'd expect the MAX() function to give you what you need.

=MAX(SUM(A1:A5),0)

MAX() returns the maximum value in a list of arguments. In this case, if the SUM() returns a value less than zero (i.e., negative) it'll return the zero instead.

Best Answer

Related Solutions

Google Sheets – Sum of Two Numbers with a Minimum of Zero

Google-sheets – How to zero out cell if filtered sum is less than 0

Related Topic