Conditional Formatting - If three cells are equal

**per11** · 08-20-2016, 09:52 AM

Hello,

I have been designing a spreadsheet to produce the lowest prices of two companies on multiple items. I have been able to conditionally format cell J2 to tell me which of the two companies are cheapest. [The formula in J2 is =IF(ISBLANK(F2),"",(IF(F2<H2,F2,H2)) ]. I have used the formula to conditionally format the cell based on their value based with [=J2=F2] then format orange. Then a separate formula for [=J2=H2] then format blue.

The problem I am having is if all three are the same. This is possible as the items can be a set price so it does not matter which supplier the item comes from. I have tried using an if formula but this is has not worked, the formula I used was [J2=H2=F2].

Is there a way to format the cell to give either of these three options?

Many thanks,

PER11

**AliGW** · 08-20-2016, 09:54 AM

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Glenn Kennedy** · 08-20-2016, 09:58 AM

Try this:

=(AND(J2=H2,H2=F2))

**per11** · 08-20-2016, 11:17 AM

Many thanks Glenn. The "And" formula worked.

**Glenn Kennedy** · 08-20-2016, 11:25 AM

Glad to have helped.

**forilohud** · 08-20-2016, 01:01 PM

Easiest way, make conditional formatting to cell value equal to =min(cells)

Remember to use $ in cell address, so the formula correctly copies to adjacent cells.

[img]PLLS3dG.jpg[/img]

**per11** · 08-21-2016, 05:54 PM

@Glenn if you needed to add an extra column, say L2, would would the formula be =(AND(L2=J2,J2=H2,H2=F2)) . I have tried and I cannot get it to work.

**ELeGault** · 08-22-2016, 12:18 PM

I would go with the Average in this case... The average of any quantity in which all parts are equal is one part. Therefore an average of all parts should be equal to your choice of which part for comparison... I will compare F since it is the first in your array.

Please Login or Register  to view this content.

This means that you can continue to add any cell to the Average() and it will become TRUE if all contained within are equal!

Do Note - That your effected range should include all parts you wish to highlight when TRUE or just a Resulting location when true. Probably best to keep it to once cell (Column) so that as you scale the formula you do not need to scale the range effected as the effected range will likely become lost in your setup.