Inventory Spreadsheet formula

**wavechaser** · 11-05-2009, 08:21 PM

I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.

What I want to do is have three columns that interact with eachother.

1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status

What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".

So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".

Any help would be appreciated.

**martindwilson** · 11-05-2009, 08:39 PM

in c1 =IF(A1<=B1+(10%*B1),"0ut","in")
where a1=stock
b1=level
conditional format is
=A1<=B1+(10%*b1)

**shg** · 11-05-2009, 08:39 PM

Welcome to the forum.

You're more likely to get useful help if you post a workbook and explain the behavior you want in context, with some examples.

**Puzzel** · 11-05-2009, 08:48 PM

This isn't pretty and I'm sure there will be more suggestions for ways to do this easier. But here is my try

**wavechaser** · 11-05-2009, 08:51 PM

Wow, thanks guys, i'm going to give it a shot right now.

**wavechaser** · 11-05-2009, 08:54 PM

That spreadsheet works perfectly, thank you! I have to figure out how the formula works myself so I can use it in my spreadsheet, but yeah, that's exactly what I want it to do.