Results 1 to 4 of 4

Thread: Conditional format based on multiple criteria

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional format based on multiple criteria

    hi folks,

    Struggling with this one and not sure there's actually a solution, but i'd love to be proven wrong (gauntlet, thrown.)

    I am compiling a Worksheet KPI that reports back a ranked list of performances, based on that areas stock holdings. Each of our stock holding areas hold a different number of line items because of their size, and our KPIs show performance on a number of factors based on these, e.g. Non-moving Stock over 30 days as shown below - rank 1 is worst performing.

    Non Movers >30 days
    Rank
    Storage Area Name Non-Moving Items
    1 Main Depot 1999
    2 Back warehouse 1446
    3 Store 2 208
    4 Store 1 12
    5 Shop floor Stock 5

    This KPI will need to take into account that "Main Depot" holds 10000+ items, "Back Warehouse" holds 3500 items, "Store 1" and "Store 2" each hold 2500 items and the "Shop Floor" holds roughly 250 items.

    So, for example, we would expect "Main Depot" to have no more than 250 non-moving items, with more 1000 being a bad result and colouring the cell red using a conditional format and anything above 500 but below 1000 as amber. The same would for apply to "Back Warehouse", where we would expect no more than 75 items to be non-movers, colouring that cell red or amber dependent on the cell value. This would apply all through the other areas with their respective non-moving item expectations. In short, whatever their place in the ranking, the total non-moving cell must colour itself dependent on the area where that stock refers to using a 2-colour scale; does that even make sense??

    If anyone out there can make heads and tails of this conundrum and thinks that there might be a simple solution, please do let me know!
    Last edited by si3po; May 9th, 2019 at 10:45 AM.

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Connecticut
    Posts
    332
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional format based on multiple criteria

    The 'relatively' easy solution is to set up your rules on the spreadsheet somewhere — out in the open, where it can be easily viewed, reviewed and revised when necessary — instead of hidden inside a CF formula. That way, your CF formula can refer to the clearly visible rules on the sheet. Then the sheet and the rules can be more easily changed as conditions change and stock levels ebb and flow or products are revised, whatever, and it's all perfectly obvious.

    So, for example, you could set up an area on the sheet called "KPI conditions" where your rules are codified along the lines that you have outlined above, and then the rules can be easily applied line-by-line (using a fairly simple formula) against each of the storage areas. If you try to hard-code the various conditions into your CF formulas, then even if you get it right today, you'll be struggling forever afterward to keep up with changes across multiple CF formulas.

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional format based on multiple criteria

    Quote Originally Posted by BlueHornet View Post
    The 'relatively' easy solution is to set up your rules on the spreadsheet somewhere out in the open, where it can be easily viewed, reviewed and revised when necessary instead of hidden inside a CF formula. That way, your CF formula can refer to the clearly visible rules on the sheet. Then the sheet and the rules can be more easily changed as conditions change and stock levels ebb and flow or products are revised, whatever, and it's all perfectly obvious.
    i like this idea, and as you say, it allows easy manipulation of the data later down the lines as total stock lines held grows/shrinks in the various store areas... an option i hadn't considered, so thank you again for the suggestion!

    So, for example, you could set up an area on the sheet called "KPI conditions" where your rules are codified along the lines that you have outlined above, and then the rules can be easily applied line-by-line (using a fairly simple formula) against each of the storage areas. If you try to hard-code the various conditions into your CF formulas, then even if you get it right today, you'll be struggling forever afterward to keep up with changes across multiple CF formulas.
    With regards the CF conditions, i assume we are talking of using '=IF(x,y,z)' formula to say "If storage area = *whatever* AND lines >= *max expected non-moving lines*, then colour cell.

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Location
    Connecticut
    Posts
    332
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional format based on multiple criteria

    It seems in general that your KPI rankings are based on having lower and lower percentages of Non-Moving Items in each warehouse, so you could set up the data that way, to show "Total Inventory" (or "Category Totals" if you want to break it down by the categories of goods or SKUs, for example) compared to another column for "Non-Moving Items", to give your percentage of Non-Movers compared to the total. Then your Rankings could be set up on a very elementary table to show Rank 1 at the highest percentage of Non-Movers, moving on a scale of decreasing percentages to the High-Movers. And your CF then only has to compare against that table. That's the simple idea; you could make it more complex as you go, say, by adding more complexity, such as item margins, total valuation, etc.

    The best thing about using KPIs is when they are clear and observable by all who have an input into changing them, so that people can maintain focus on what management deems to be important.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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