Limit to Whole numbers, no negative #

[kzitil]

I have constructed an inventory formula, subtracting Current Inventory (CI) from Required Inventory (RI), to generate a number of how many new products must be Ordered: RI-CI=O. The problem is that if CI exceeds RI, I get a negative number for "O".

Question: How can I structure the formula to give a zero for any sum less than 1?

[oldchippy]

Hi,

Welcome to the forum, try something like this

=IF(B1-A1<0,0,B1-A1)

[kzitil]

Hi Oldchippy and thanks for the welcome and suggestion.

Unfortunately, the function "=IF(K17-J17<0,0,K17-J17)" is classified as "invalid" by Excel's Function Arguments. I have only rudimentary skills with Excel so, if you have any other suggestion or, alterantely, if I made any typo-kind of error(s), I'm all eyes! Thanks in advance!

[shg]

It's a perfectly valid formula if those two cells contain numbers, not text.

[martindwilson]

or
=MAX(K17-J17,0)

[kzitil]

Sorry folks, I get the same error message using either formula and I double checked both the K17 & J17, as well as the target cell and all are formatted as Number cells with no decimel places. Any other suggestions?

Thanks again!

[shg]

Try increasing the decimals by changing the formatting; if they don't change, they're stored as text.

[martindwilson]

attach a workbook with the non working formula leave the = off so it can be added in when we look at it

[kzitil]

Hello Martin, SHG and Old Chippy,

Sorry for the delay. As suggested, please find attached a reduced sample of the worksheet I was working on. Your reviewing and modifying to achieve the end I've been seeking is appreciated.

[martindwilson]

well it works for me!
ive activated the function and am posting back,

[oldchippy]

Hi,

Put this in cell C9

=IF(K9-J9<0,0,K9-J9) then with the cell selected auto-fill downwards to C16 by grabbing the little black square in the bottom right hand corner of the cell. All will be revealed

[kzitil]

Hi Martin and Chippy,

Thank you both very much. As often is the case, the problem was minor: my using a comma instead of a semicolon!

Martin
=MAX(K17-J17;0)

Chippy
=IF(K10-J10<0;0;K10-J10)

Is there any advantage of using one formula over the other? Many thanks again!

kzitil

[martindwilson]

mine has a lower carbon footprint and in the current economic crisis (doh!), it saves on keyboard wear and tear .

[oldchippy]

mine has a lower carbon footprint and in the current economic crisis (doh!), it saves on keyboard wear and tear .

Nice one Martin

[kzitil]

Thanks again, Martin & Oldchippy,

For sharing your wisdom and offering prompt help.

By the way, I didn't know Carbon has feet. Do other elements as well?

All the best!