# Trying to Write a formula to calculate available inventory

1. ## Trying to Write a formula to calculate available inventory

Need help to write a formula in cell K9 to add the highest value without causing errors.

We have a current QTY of 30 cases on the floor. We have replenishment orders that will add additional inventory, but have also reserved product for future orders. We can still add an order, but for how much? I figured this is 20, but need a formula to determine easily.

Can someone help me please?

Thanks
Adam

2. ## Re: Trying to Write a formula to calculate available inventory

Maybe this:

=SUMIF(\$2:\$2,"Replenish",\$9:\$9)+SUMIF(\$6:\$6,"Current",\$9:\$9)-SUMIF(\$2:\$2,"Reserved",\$9:\$9)

3. ## Re: Trying to Write a formula to calculate available inventory

Mr. Kennedy,

You are a gentleman and a scholar. Thank you!

Best,
Adam

4. ## Re: Trying to Write a formula to calculate available inventory

And you're not a bad chap, either!!

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

5. ## Re: Trying to Write a formula to calculate available inventory

Thank you, Sir!

So.. it seems this didn't quite work as we'd hoped... once I removed a future reserve, an error popped up.

Still trying to work this one out! If you have any additional thoughts, they'd be more than welcomed!

Best,
Adam

6. ## Re: Trying to Write a formula to calculate available inventory

Sh1t happens. I'll take a look.

7. ## Re: Trying to Write a formula to calculate available inventory

Maybe this in L9:

=E9-SUMPRODUCT((ISNUMBER(F6:K6))*F9:K9)

8. ## Re: Trying to Write a formula to calculate available inventory

Unfortunately cant change any of the formulas in the row..

L9 calculates the inventory at a future date. So if we have a new order, we'd only be able book 20 (in K9) without triggering errors (causing future inventory to be <0)

9. ## Re: Trying to Write a formula to calculate available inventory

Going out for dinner now. How can I get it to work, without changing formulae.... either current or new??

10. ## Re: Trying to Write a formula to calculate available inventory

Is there some sort of formula that works similarly to a what-if? Meaning, something that can hit a MAX number without causing "ERROR" in a row?

11. ## Re: Trying to Write a formula to calculate available inventory

Just saw your note. I just asked a coworker to take a look as well.. It's not an easy one.

Bc of the way this all works, the only cell that can be altered (besides the hard-coded) is K9... total pain!

I think my report is going to have to be more complicated than originally anticipated.. Such is life. Can't thank you enough, Glenn.

12. ## Re: Trying to Write a formula to calculate available inventory

Finally figured it out..

See attached FYI.

Simple as a MIN formula.. Just needed to rack my brain a bit.

Thank you VERY MUCH!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1