Excel conditional formatting if two condition satisfies

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
Untitled.png



Microsoft office version 2016
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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....
 

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,156
Messages
5,629,019
Members
416,359
Latest member
Juena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top