conditional format using index or mod equation

dmheller

Board Regular
Joined
May 26, 2017
Messages
142
Office Version
  1. 365
I have some data it starts in E5. I added a screen shot too to show how the cells are merged. I am wanting to conditional format column F that is the merged cells to see if it is between L and M values, also merged cells the same 5 rows. I dont know much about conditional formats. I know I can use index or mod but don't know where to put the equation or what to highlight when i put it in. I watched excel magic and am still lost. I had something like =index(F:F,(row()+41)/5))
this is the same =INDEX(F:F,(ROWS($B$4:$B4)-1)/5+ROW($F$9))
Any help would be great.
thanks

1616010693590.png

36.8136.83
37.05
36.78
36.75
36.75
36.2836.39
36.47
36.75
36.22
36.23
24.8824.76
24.66
24.78
24.73
24.77
36.0235.92
35.89
35.90
35.75
36.05
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
to see if it is between L and M values
There is nothing in your post to tell us what L and M values are.

Merged cells are going to create more problems than you think they might solve. In your screen capture, the value of 36.83 in the first merged cell is only directly comparable to the value of 36.81 in the top cell to the left. The merged cell will have a value of 0 when compared to the other 4 adjacent values.

It might be possible to work around it with some messy formulas but there is not enough information to make an accurate assessment of the requirement.
 
Upvote 0
Sorry, L and M are just numbers L and M are the last 2 columns here. As you mentioned, the first merged cell is 36.83. That is the average of the non merged cells.
1616012345903.png
 
Upvote 0
In that case, the solution is quite simple. Assuming that the first merged cells start in row 2, use this as your CF rule.
Make sure that F2 is selected when you create the rule, otherwise you can end up with relative offsets and the wrong rows being formatted.
Excel Formula:
=$F2=MEDIAN($F2,$L2,$M2)
 
Upvote 0
In that case, the solution is quite simple. Assuming that the first merged cells start in row 2, use this as your CF rule.
Make sure that F2 is selected when you create the rule, otherwise you can end up with relative offsets and the wrong rows being formatted.
Excel Formula:
=$F2=MEDIAN($F2,$L2,$M2)
so here is where i am dumb with conditional formats. you said hightlight F2. Its F5 no big deal but then i put it in as a formula then what do i do? I played with it and did this
=$F$5:$F$64
this is in the applies to. Now for another question. It is highlighting things between the numbers. I messed up i mean outside the L and M
i think i got it thank you. I just did <>
everything seems good. Thank you so much
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,697
Members
448,293
Latest member
jin kazuya

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
Back
Top