Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a Google Spreadsheet with two columns of numbers (A & B). I want to add these numbers together and put the result into a third column (Results).

If the sum of these two numbers is less than zero, I would like the result column to have zero instead of a negative number.

I found this list of Google Spreadsheet function,but I did not see one that is applicable.

*----*----*--------*
| A  | B | Results | Calculation in result column:   =Sum(A1:B1) 
*----*----*--------*
| 1  | 3 |       4 | <= Normal and good, 
*----*----*--------*
| 1  |-3 |       0 | <= Good, this is what I am looking for 
*----*----*--------*
| 1  |-3 |      -2 | <= Bad, this is what is happening.  
*----*----*--------*

My question is:

  • How do you find the sum of two columns that will result in a minimum value?
share|improve this question

migrated from superuser.com Sep 22 '13 at 11:16

This question came from our site for computer enthusiasts and power users.

3 Answers 3

up vote 7 down vote accepted

You can use the MAX function for this, since

MAX( A3+B3, 0 )

will return 0, when the sum is negative.

share|improve this answer

In openoffice, I used the following:

 if(sum(a1:b1)>0; c1; 0)

This formula is placed in c1. It says that if the sum of A1 through B1 is greater than 0 then use the answer that is in C1 otherwise use 0.

I'm just assuming that googles' spreadsheet has somehthing similiar. I hope this works for you.

share|improve this answer
    
Better would be to know that in advance. –  Jacob Jan Tuinstra Sep 22 '13 at 15:03

This is the ARRAYFORMULA variant:

=ARRAYFORMULA(IF(A1:A3+B1:B3<=0;0;A1:A3+B1:B3))

I didn't get the MAX to work with the ARRAYFORMULA.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.