MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Negative Numbers to 0


Posted by Jeff on February 04, 2002 9:35 AM

I am trying to find out how to change a cell that has a negative number to 0. Is this possible?
this is my formula..

=SUM(R9-(G9*66.67))*IF((E9)="s",VLOOKUP(R9,Single_Person_State,2),VLOOKUP(R9,Married_Person_State,2))-IF((E9)="s",VLOOKUP(R9,Single_Person_State,3),(VLOOKUP(R9,Married_Person_State,3)))


Posted by Scott on February 04, 2002 12:07 PM

How about this:

=IF((SUM(R9-(G9*66.67))*IF((E9)="s",VLOOKUP(R9,Single_Person_State,2),VLOOKUP(R9,Married_Person_State,2))-IF((E9)="s",VLOOKUP(R9,Single_Person_State,3),(VLOOKUP(R9,Married_Person_State,3))))<0,0,(SUM(R9-(G9*66.67))*IF((E9)="s",VLOOKUP(R9,Single_Person_State,2),VLOOKUP(R9,Married_Person_State,2))-IF((E9)="s",VLOOKUP(R9,Single_Person_State,3),(VLOOKUP(R9,Married_Person_State,3)))))