Conditional format based on multiple criteria

[si3po]

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.

[TABLE="class: outer_border, width: 507, align: center"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Non Movers >30 days[/TD]
[/TR]
[TR]
[TD]Rank
[/TD]
[TD]Storage Area Name[/TD]
[TD]Non-Moving Items[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Main Depot[/TD]
[TD]1999[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Back warehouse[/TD]
[TD]1446[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Store 2[/TD]
[TD]208[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Store 1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Shop floor Stock[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[BlueHornet]

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.

 

[si3po]

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.

 

[BlueHornet]

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.