Excel conditional formatting if two condition satisfies

[adpatel]

I have excel sheet, which has value stored as price in "D" column

now, I want to highlight particular cell in column "D" which satisfies below 2 conditions

1. XFB9<=0.02
2. which cell has highest value among cells that has fulfilled above condition (column "D")

issue I find here in writing formula is, to define range Because, how do I define range to get highest value from that cell which fulfills no. 1 condition?, because it varies time to time

I have written following formula in conditional formatting for no. 1 condition

=XFB9<=0.02
Kindly help with your solution on this issue, any help highly appriciated.

 

[jasonb75]

You might need to give a clearer impression of your sheet layout, XFB9 is a single cell which is unlikely to be used for anything. There is no indication of how that relates to column D in your post.

 

[adpatel]

Microsoft office version 2016

 

[jasonb75]

Allowing for data from rows 9 to 100, try.

=AND($XFB9<=0.02,D9=AGGREGATE(14,6,D$9:D$100/($XFB$9:$XFB$100<=0.02),1))

 

[adpatel]

for cell D9, condition of 2% to be checked in cell XFB9 for cell D10 it's to be checked in cell XFB10, for D11 check in XFB11 and so on....

 

[jasonb75]

That is what it is doing.

 

[adpatel]

Will this work on excel 2007 as well? I have checked on excel 2016 it's working fine! you are genius bro.

 

[adpatel]

I have tried in excel 2007, but in that version it's not working, if you can help on this as well, I would be grateful to you.

 

[jasonb75]

Will this work on excel 2007 as well?

It will work with 2010 or newer, this version should work with 2007 as well.

=AND($XFB9<=0.02,D9=LARGE(IF($XFB$9:$XFB$100<=0.02,D$9:D$100),1))

 

[adpatel]

It will work with 2010 or newer, this version should work with 2007 as well.

=AND($XFB9<=0.02,D9=LARGE(IF($XFB$9:$XFB$100<=0.02,D$9:D$100),1))

It's working excel 2007, thank you so much, you made my day!