Making cell 0 if less than 0

[ardykav]

Hi and Happy new Year to you all,

Would just like to know how what i need to add to a formula so that if the result is less than 0 it returns a 0 value?

Currently i have the following:

=IF(A4<>"New Car","N/A",IF(AND(A4="New Car",U4<>"Sold"),($AE$1-$M4),IF(U4="Sold","")))

 

[ZVI]

Hi, just replace ($AE$1-$M4) by MAX(0,$AE$1-$M4)
Happy New Year to you too!

 

[tygrrboi]

Try this

=IF(A4<>"New Car","N/A",IF(U4="Sold","",IF(($AE$1-$M4)<0,0,($AE$1-$M4))))

 

[Michael M]

try

=IF(A4<>"New Car","N/A",IF(AND(A4="New Car",U4<>"Sold"),IF(U4="Sold","",IF($AE$1-$M4<0,0,$AE$1-$M4))))

 

[ardykav]

Thats perfect thanks a lot mate and all others who rrplied, greatly appreciated,

Try this

=IF(A4<>"New Car","N/A",IF(U4="Sold","",IF(($AE$1-$M4)<0,0,($AE$1-$M4))))

 

[tygrrboi]

For future reference...

You do not need to include something you already checked for in the nested if. For example:

You had

=IF(A4<>"New Car","N/A",IF(AND(A4="New Car",U4<>"Sold"),........etc

Inside the first if we checked if A4 is <> "New Car" in the Else (or Value if False) section you only need to check for U4 and "Sold"
If the formula has gotten that far it means that A4 <> "New Car" is false, and therefore A4 = "New Car" No need to check A4 with the AND statement