Conditional Formatting - If three cells are equal but not 0

You mention 3 cells in the thread title but your formula has 4 cells?

L2, H2, F2 and J2

Conditional Formatting
Formula:

=AND($L2<>"",$L2=MIN($L2,$H2,$F2,$J2))

Do you have a sample file with this in it....
As pointed out by Tony, your comments dont match your example. Beyond that I am not sure I understand the intent... You want to format a cell when they are all EQUAL but not 0?

Tony, I am missing where or why you went to decide that L2 gets to be equal to the least value?

=AND(L2<>"",L2<>0,L2=F2,L2=H2,L2=J2)
By basing the entire condition on L we can dictate that L must not be 0 and equal to the rest of the set. This inherently makes the others equal to each other.

To me that gets you to the results you are requesting... F H and J must all equal L where L cannot be empty or 0

If this is not the goal then add a working example book with multiple rows showing different scenarios and something changing in color when the desired "TRUE" is hit

Originally Posted by ELeGault

Tony, I am missing where or why you went to decide that L2 gets to be equal to the least value?

I may have misunderstood the requirement.

I read this:

show which item is cheapest

To mean they want to highlight the min value.

Forgive me I could have made that clearer. L2 is the minimum number but zero of the given range as you will see from the attached workbook.

The cheapest price can either be, L2=J2=H2=F2 or in the example shown, L2=H2=F2 and J3=0. Or a given combination of H2 or F2 =0. So presumably the formula will need to take this into account or the formulae should be separate but format the cell in the same way?

I hope this was helpful.

Sorry, I downloaded your file but I have no idea what you're wanting to do!

Sorry, I will try again! (I have swapped the sheet with a different with all the conditionally formatted rules to make it easier).

L2 is the minimum price of the three given cells. This price could be the lowest price from one provider and this is formatted correctly at the moment.

However the price might be the same from two or three of the sellers. To complicate matters further, one of the cells might be a zero whilst the two other prices are equal.

I hope this makes it clearer.

Per11.

Thanks for all your help. I have worked out what I was trying to do and to demonstrate this I have uploaded a simpler version of the completed spreadsheet.

First I created a circular argument if all the cells are equal and I used the formula, =AND(L2=F2,F2=H2,H2=J2,J2=L2) and this returns a fill for the cell if this is true. This formula must be at the bottom of the list to make sure this is processed last.

Then I worked out what every possible combination of the cells could be, bearing in mind that L2 is always stable given the formula in the cell. Therefore, the combinations for the formatting are:

L2=F2=H2 less than J2
L2=F2=J2 less than H2
L2=H2=J2 less than F2

Then you use an "and" function to determine the possible combinations. The standard one in this instance is, =AND(L2=Cell1,Cell1=Cell2,Cell2 less than Cell3 . Therefore the possible combinations are:

=AND(L2=F2,F2=H2,H2 less than J2)
=AND(L2=F2,F2=J2,J2 less than H2)
=AND(L2=H2,H2=J2,J2 less than F2)

(N.B. It would not let me us shift , so that should be the the "less than" should be the less than sign)

And then conditional formatting does the rest. Although this way of doing things, you end up with a lot of formulae, which may not be helpful to some.

I hope that this is a lot clearer then when we started! If the community would find it useful I can release the whole formatted spread sheet?

Thanks again,

PER11

referring to l2 is unnecessary in your example and may cause your circular referencing

you have 7 scenarios from what i can see
1,2 or 3 lowest
1-2 match
2-3 match
1-3 match
all 3 match

this formula will extract 1-7 out for you of which you can conditional format all 7 outcomes

Formula:

Please Login or Register  to view this content.

ps 1-3 returns as text