+ Reply to Thread
Results 1 to 2 of 2

Formula and conditional formatting for checking that stock levels meet customer demand

  1. #1
    Registered User
    Join Date
    03-22-2014
    Location
    Sheffield, England
    MS-Off Ver
    MS Office 2010
    Posts
    1

    Formula and conditional formatting for checking that stock levels meet customer demand

    Morning,

    I'm a bit of a novice with excel, self taught so far. I have attached a small extract from a spreadsheet that I am developing that may help to illustrate my problem.

    I want to use the sheet to check customer demand (along with required delivery date) and compare that to a cell containing our current stock level for that product. I would like to use conditional formatting to show whether or not we have enough parts in stock to meet demand. It would be nice if the formula also had a buffer to highlight that we may be able to meet demand but that we would be cutting it close. For example, if we have 32 parts in stock and the customer demand was 30.

    Thanks in advance for any help

    Dan
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Formula and conditional formatting for checking that stock levels meet customer demand

    for Green use
    =G4+5<E4
    stock left will be above 5

    For Yellow
    =AND(E4-G4>=0,E4-G4<=5)
    Stock is between 0 and 5 left

    for Red not enough stock to cover order
    =E4<G4

    change the 5 to whatever number you want to use as a minimum buffer size
    see attached , you could use icons for conditional formatting and show whats left in stock if you wanted to
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula for Running Balance of stock levels
    By lindafinlay in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2013, 12:09 AM
  2. Identify the earliest expiry date on stock based on current stock levels
    By julie86xx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2013, 01:08 AM
  3. updating stock levels using a formula
    By kompshop in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-04-2013, 11:14 PM
  4. [SOLVED] Formula help required to enable conditional formatting of cells that meet multiple con
    By Barbara Excel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-14-2012, 10:10 AM
  5. Replies: 2
    Last Post: 10-28-2011, 11:58 PM

Bookmarks

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