Conditional Formatting with Filtering - How to highlight the cell with the MIN value of a caculation-CodePudding

Conditional Formatting - How to highlight the cell with the minimum value based on the result of two other cells. I want to highlight the cell in selected cells in a Row that corresponds to the smallest product in the each column. (Note all value in the rows will be equal to or greater than zero)

Example: Imagine Cells A1:E2 are all filled with numbers, with at least one cell equalling to zero. Here Cells A2, C2, or E2 can be highlighted. Cell E2 is highlighted as E1E2 is the smallest non-zero product out of the products A1A2, C1C2, E1E2

So far I have used the formula

=A$1A$2=MIN($A$1$A$2,$C$1*$C$2,$E$1*$E$2) applied to A2,C2,E2

Excel 2021

CodePudding user response：

Can more than 1 of the 3 fields be = 0? If only 1 or none = 0, then this works. I've formatted it for clarity since there are multiple nested if's.

=A1*A2=IF(  $A$1*$A$2=0, 
            MIN($C$1*$C$2,$E$1*$E$2), 
            IF(  $C$1*$C$2=0, 
                 MIN($A$1*$A$2,$E$1*$E$2), 
                 IF(   $E$1*$E$2=0, 
                       MIN($A$1*$A$2,$C$1*$C$2),
                       MIN($A$1*$A$2,$C$1*$C$2,$E$1*$E$2)
                  ) 
            )
        )

CodePudding user response：

OK, so what you need to do logically is to exclude any 0 values from the MIN calculation. You can do that by testing each product, and if 0, change it to a number larger than any possible normal value (I used 9999). This works:

=A1*A2=MIN(
    IF($A$1*$A$2=0,9999, $A$1*$A$2),
    IF($C$1*$C$2=0,9999, $C$1*$C$2),
    IF($E$1*$E$2=0,9999, $E$1*$E$2))