CODEWITHSUNDEEP
excelexcel-2007excel-formula
dwyane
dwyane

Reputation: 327

Range Limiting the result of a formula

How do I Make a range of Cells to Zero If a calculation gives A Negative Number?

Example: =D12-D19

Answer: -1

I would like to replace the negative to zero.

Upvotes: 2

Views: 9626

Answers (2)

iDevlop
iDevlop

Reputation: 25262

More efficiently, you could use: 

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

Upvotes: 3

Bart Kiers
Bart Kiers

Reputation: 170178

Try this:

=IF(SUM(A1:A5) < 0, 0, SUM(A1:A5))

or depending on your locale:

=IF(SUM(A1:A5) < 0; 0; SUM(A1:A5))

which means:

IF the sum of the cells A1 + A2 + A3 + A4 + A5 is less than 0
  make it 0
ELSE
  make it the sum of the cells A1 + A2 + A3 + A4 + A5

Upvotes: 1

Related Questions