if and sum

[i8ig]

I need a formula which says if(sum(b1-a1) is less than 0 then display 0, if greater then display the sum of b1-a1

any suggestions?

 

[VoG]

Try

=MAX(0,B1-A1)

 

[i8ig]

Thanks, worked good in 1st cell, but other cells without a value in them produce a value error, any other suggestions

 

[VoG]

That works for me with blank cells. Maybe they contain spaces or similar. Try

=IF(ISERROR(B1-A1),0,MAX(0,B1-A1))

 

[i8ig]

That helped, however if b1 has a value in it and c1 does not, I still receive a negative number Is there a way to have either a blank or 0 show up in the cell with the formula in it?

 

[VoG]

With either cell empty I do not get negative numbers. Can you give an example that produces a negative number.

 

[i8ig]

cell a2 contains the formula =sum(b1)

using the formula provided a negative number is returned. formula provided is in cell d

cell a2 contains the number 11, when the formula provided is inserted in cell d2 a value of -11 is returned

 

[VoG]

I can't reproduce that

Excel Workbook
	A	B	C	D
1		11
2	11			0
Sheet3

 

[i8ig]

Thanks, still get a negative number, however if I move down and start a new series the 0 is returned properly

 

[Bilseka]

thank you