Conditional formatting formula - multi criteria please

Orangeanorak said:

1. Formula please to highlight the cell in column H that is equal to or nearest to J1 IF the cell in the same row in column J equals 7

Click to expand...

How much near to the value of J1?
I mean there is only 1 reference we are getting for each row.
i.e 8 is nearest to 7.
but 22 is also nearest to 1 (If there is no other value to compare !!!)

so, is there any definition of "Nearest" ? I mean +/- 3 or something like that?




on upper side or Lower side?

Nearest on greater side , or lesser side or just nearest?

Thank you for your reply - I hope this explains it better.

In the example above I enter a value manually in J1- in this example it was 17.

The values in H4:H13 are entered manually
then when data is entered elsewhere in the spreadsheet the values in J4:J13 will increase.
When a value in a cell in J4:J13 reaches 7 I require the cell/s in the same row in Column H to be highlighted

The problem bit is when more than one cell in J4:J13 reaches 7 then I require the cell/s in the same row in column H to be highlighted that has a value nearest to the value in J1 (17) (upside and downside)
In this example J4 ,J5 and J7 have reached 7 but only the cells H5 (8) and H7 (8) are highlighted because the values in them are closer to the value in J1 (17) than the value in J4 (7) -- {because 8 is closer to 17 than 7}


If the value in J1 had been 3 then H4 would have been highlighted as the value in H4 (7) is closer to the value in J1 (3) than the values in H5 (8) and H7 (8)

Does that make sense

Thanks again for your time

Try this.
1. Select B4:B10 and H4:H10, ensuring either B4 or H4 is the active cell.
2. Apply the CF shown.

Use this formula in both the columns H and B for conditional formatting.

=MIN(IF($J$4:$J$13=7,ABS($J$1-$H$4:$H$13)))=ABS($J$1-$H4)

Peter_SSs said:

Conditional formatting

Cell Nr.: / Condition Format B4 1. / Formula is =AND($J4=7,ABS($J$1-$H4)=MIN(IF($J$4:$J$13=7,ABS($H$4:$H$13-$J$1)))) Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Click to expand...

Sir,
Can't we avoid AND function?

Because MIN(IF will take care of the essential condition J4=7 by excluding other values from comparison...

Select H4:H13 and run the following formula in CF...

=$H4=INDEX($H$4:$H$13,MATCH(MIN(ABS(IF($J$4:$J$13=7,$H$4:$H$13)-$J$1)),ABS(IF($J$4:$J$13=7,$H$4:$H$13-$J$1)),0))

Select B4:B13 and run the same formula.

Shrikant said:

Sir,
Can't we avoid AND function?

Because MIN(IF will take care of the essential condition J4=7 by excluding other values from comparison...

Click to expand...

No.

With the sample data as shown in my sheet, but with your CF formulas in place, change any of the other cells in column H to 8

Aladin Akyurek said:

Select H4:H13 and run the following formula in CF...

=$H4=INDEX($H$4:$H$13,MATCH(MIN(ABS(IF($J$4:$J$13=7,$H$4:$H$13)-$J$1)),ABS(IF($J$4:$J$13=7,$H$4:$H$13-$J$1)),0))

Click to expand...

Aladin, that does not address the highlighted condition below

Orangeanorak said:

1. Formula please to highlight the cell in column H that is equal to or nearest to J1 IF the cell in the same row in column J equals 7

Click to expand...

Right.

Initially I also used AND.
Then this diabolical IDEA came to me to avoid AND.