Comparative Analysis of Rates using Conditional Formatting

Highlight Cells values in between Lower and Upper Limits

Have you ever wanted to filter or Highlight items in a dataset  automatically for cells values that falls in between two given limits (USL and LSL) !!. 

It is particularly helpful in Comparative analysis of rates, to get atleast [3] suppliers with lowest prices that falls within a 10% price variation from a list of suppliers. Conditional formatting plays an important role to show the final output as Yellow highlighted cells.

[SMALL] function is used to calculate boundary conditions or as input data for conditional formatting in excel.  Simple excel VBA code can even make it faster if it has to be done for number of times.
Also Read :  Print Large Posters and Banners on standard A4 printers

Download it Here  :   Analysis of Rates | Google Sheets version 

Let us see How to do it in Excel Worksheet : 

Just add three columns descriptions as Nth Sequence, LSL and USL at the end of price data in column R, S & T respectively.

Cell (R4) is a  "Sequence no." (i.e 1,2,3 . ..) to denotes Nth  order of minimum value.  for example, '1' is the lowest value and '2' is second lowest value and so on . . . 

Sample Table with price data from different Suppliers/Make

LSL (Lower specification Limit) formula in Cell (S4) 

                                      S4  =  SMALL(C4:Q4, R4)  . . . [ Calculates the Nth the smallest value]

USL (Upper specification Limit) formula in Cell (T4) 

                                      T4  =  S4+10%*S4              . . . [ for a 10% price variation]

Conditional Formatting on Cell Values between LSL and USL

Home > Conditional Formatting > New Rule

Select <Format only Cell that contains> <Cell Value> <Between>

Select Format > Fill > Yellow > OK.

Now, Conditional formatting is applied,  but just two suppliers meets the 10% price band limits at lowest  price as LSL.

Revise Nth Sequence no to '2' for second lowest value as LSL and See the output.

Keep changing sequence no till you get 3 supplier or the conditions fails for price range.

Use below excel VBA code to define Shortcut keys

Sub Min_value_format()
    Dim LSL As String
    Dim USL As String
    Cells(Selection.Row, 18).Select
    ActiveCell.FormulaR1C1 = "1"
   
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=SMALL(RC[-16]:RC[-2],RC[-1])"
   
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-1]*10%"
    Range(Cells(Selection.Row, 3), Cells(Selection.Row, 17)).Select
   
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$S" & "$" & ActiveCell.Row, Formula2:="=$T" & "$" & ActiveCell.Row
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
   
End Sub