# Excel formula is stock sheet

Copper Contributor

# Excel formula is stock sheet

Hi, I have a stock ordering sheet where the stock in hand is 4.25 and par stock is 5 order number is .75 round up to 1, however the order value is calculating at .75 not 1

7 Replies

# Re: Excel formula is stock sheet

You mean this doesn't work for you (see picture below)?

# Re: Excel formula is stock sheet

Thanks of course it works!! I was using IF

# Re: Excel formula is stock sheet

@KenH99 Just realized that you want to avoid a negative reorder qty if the qty on hand is larger than par.

Then you may use:

=MAX(0,ROUNDUP(B2-A2,0))

# Re: Excel formula is stock sheet

Thats great thanks, i have a new issue when the on hand stock is 90 and the par stock is 96 but the re-order until is 24 how do i do that?

# Re: Excel formula is stock sheet

@KenH99 Sorry but I don't understand "the re-order until is 24". If the re-order level is not equal to par, what is it?

Perhaps

=MAX(0,CEILING.MATH(B2-A2,24))

# Re: Excel formula is stock sheet

Sorry Riny your being extremely helpful and i havent explained very well.

the order unit is 24
so if 90 in hand and par stock is 96 re-order is 0 but its calculating 6 in the column for order value?

# Re: Excel formula is stock sheet

@KenH99 Sorry for being slow, but if the qty on hand is less than par, shouldn't you re-order then? I assumed so, thus when you are 6 short you need to re-order 24. That's what the last formula does.

If you mean that you don't re-order until you are at least 24 short, perhaps this is what you need:

=IF(B2-A2>=24,MAX(0,CEILING.MATH(B2-A2,24)),0)

I'm just guessing.