0

I have a sheet where in alternating columns I list Supplier pack size and price per unit. I want to highlight the cell(s) in this range with the lowest price per unit, ignoring zeros. How can I do this?

Currently, I have 3 formatting rules:

=MOD(COLUMN(A2), 2) = 1 (No format, stop when true)
=A2 = 0 (No format, stop when true)
=A2=SMALL(A2:F2,COUNTIF(A2:F2,0)+1) (Green background)

Unfortunately this doesn't work if the smallest value above zero happens to be eliminated by the first rule.

How can I modify the formula so that it effectively ignores the values in the Pack Size columns? Alternatively, is there another way of doing this?

This is what I currently have:

What I have

After applying the conditional formatting this is what it should look like:

Lowest PPU highlighted

1 Answer 1

1

First calculate the smallest price without 0 like the following in I1 for example

=SMALL(IF((--MOD(COLUMN(A2:H2),2)=0)*(A2:H2)>0,(--MOD(COLUMN(A2:H2),2)=0)*(A2:H2),""),1)
and press Ctrl + Shift + Enter at the same time
It is an array formula
After having the smallest price select the Data where you want to apply conditional formatting and choose the rule :
=A2=$I$1*(--(MOD(COLUMN(),2)=0))
Choose the format you want (you can just write =A2=$I$1 to A2 in conditional formatting rule and choose the format, later use Format Painter to copy the conditional formatting to other prices)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .