Make negative number equal zero with an IF function, however then adding on to the zero

[kc90]

I have a sheet where I have a list of products, the par for each product, the inventory for each product, and the order for each product as the column headers. Specifically, in cell B17 the par is 12, in cell C17, the inventory is 13, and in cell D17 it currently has a -1 (because I automatically have it populate with a formula that subtracts cell B17-C17 to get the order for D17). If a negative number appears, I want it to read as zero.

After the "order" column in column D, I have several columns (Columns E-N) set aside for any private orders that I may have to input. In Column O I have the total. Therefore (continuing with my example) in D17 reads =B17-C17, then in O17, I have the SUM:D17-N17.
The error occurs when I try to add an IF function. If I set the function to <0, and have any additional private orders that need to be added, the orders that are added up until a positive 1 is reached.

I hope this makes sense, I could REALLY use the help. Thank you.

[Søren Larsen]

I guess in O17 you should input:

=B17-C17+sum(E17:N17)

Then you can keep your IF-statement in D17.

Helpfull?

[Andrew-R]

You have a couple of options.

In cell D17 you could have a formula along the lines of:

=IF(B17-C17<0,0,B17-C17)

Alternatively, you could use a customer format on the cell of:

General;"0"

Which will display a 0 if the cell contains a negative value, but still treat the value as a negative for sums.

I'm not quite sure what you're trying to do with O17, could you explain that a little more?

[DGagnon]

If i understand what you are trying to do you could try something like this.

in D17 you can use

=IF(B17-C17<0,0,B17-C17)

and then in O17 something like this

=IF(D17-N17<0,0,D17-N17)

if that is not what you are looking for please attach a sample workbook with desired results and i can figure it out for you.

[ConneXionLost]

in D17 try:

Please Login or Register  to view this content.

and in O17 this

Please Login or Register  to view this content.

Cheers,