XL conditional formatting Q for the gurus
Closed
Micahd
-
Mar 27, 2011 at 04:59 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Mar 29, 2011 at 06:24 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Mar 29, 2011 at 06:24 AM
Related:
- XL conditional formatting Q for the gurus
- Clear only the formatting from the selected cell (leaving the content) - Guide
- Phone formatting software for pc - Download - File management
- "Publish your article in rss format for other websites to syndicate" - Software Forum
- Cannot publish on farmville - Video Games Forum
- Publisher ✓ - Office Software Forum
6 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 27, 2011 at 08:47 AM
Mar 27, 2011 at 08:47 AM
You can conditional format all rows, and one of the condition that you need to add would be row check. taking your example
=AND(A1>200, MOD(ROW()-17,5)=0,row()>=17)
=AND(A1>200, MOD(ROW()-17,5)=0,row()>=17)
So the fx above would be the only rule, correct? Can you explain what the pieces do, so I can modify it for my use? One Q is does that fx increment to all rows 5 at a time? Is that what the 17,5 does?
To make the request more specific, here's an example of the data.
A B C D E F G
Row 17 Index 100.0 304.4 105.1 185.4 59.6
So as you know, I'd like to highlight in this case, D17 as the only value >200.
Thx very much, appreciate the help.
To make the request more specific, here's an example of the data.
A B C D E F G
Row 17 Index 100.0 304.4 105.1 185.4 59.6
So as you know, I'd like to highlight in this case, D17 as the only value >200.
Thx very much, appreciate the help.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 28, 2011 at 08:16 AM
Mar 28, 2011 at 08:16 AM
yes it is only one condition
A1>200 was your initial condition that you wanted
ROW() is a function that returns the row number
ROW() -17, is to offset the 17 rows. You wanted to start at row 17
MOD is a function that takes two parameters, the first one is number and 2nd one the divisor, and returns the remainder. So MOD(ROW()-17,5)=0 is checking if the remainder is 0, if the row-17 is divided by 5. This was added to highlight only next 5th row (if that needs to be highlighted, which also means if 5th row is not true, and 6th is, it will not highlight either 5th or 6th as we are implicitly saying that conditons needs to be applied only on 5th rows)
ROW() >=17 is to make sure that we start at row 17 and beyond (else there would other rows too that will return 0 as remainder like row 12 (12 -17 = -5 and -5/5 gives 0 remainder)
AND is to say that all these conditions should be evaluated as a group
A1 >200 and MOD(row()-17, 5)=0 and ROW() >=17
A1>200 was your initial condition that you wanted
ROW() is a function that returns the row number
ROW() -17, is to offset the 17 rows. You wanted to start at row 17
MOD is a function that takes two parameters, the first one is number and 2nd one the divisor, and returns the remainder. So MOD(ROW()-17,5)=0 is checking if the remainder is 0, if the row-17 is divided by 5. This was added to highlight only next 5th row (if that needs to be highlighted, which also means if 5th row is not true, and 6th is, it will not highlight either 5th or 6th as we are implicitly saying that conditons needs to be applied only on 5th rows)
ROW() >=17 is to make sure that we start at row 17 and beyond (else there would other rows too that will return 0 as remainder like row 12 (12 -17 = -5 and -5/5 gives 0 remainder)
AND is to say that all these conditions should be evaluated as a group
A1 >200 and MOD(row()-17, 5)=0 and ROW() >=17
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Mar 28, 2011 at 04:03 AM
Mar 28, 2011 at 04:03 AM
put your value in a cell suppose it J2
now select the cell you want to use conditional formatting. (e.g. from d1 to h10000)
now apply the conditional fomatting for greater than J2 in all cells.
now select the cell you want to use conditional formatting. (e.g. from d1 to h10000)
now apply the conditional fomatting for greater than J2 in all cells.
@rizvisa1 -
Hopefully last dumb Q. Where do we enter this fx?
Thank you.
Hopefully last dumb Q. Where do we enter this fx?
Thank you.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 29, 2011 at 03:54 AM
Mar 29, 2011 at 03:54 AM
it was for conditional format. i take it u already know about it
Didn't find the answer you are looking for?
Ask a question
Yes, maybe it's different in our version. Is this in the "manage rules" dialogue? In our v we have 1 field where we can enter a function.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 29, 2011 at 05:07 AM
Mar 29, 2011 at 05:07 AM
yes it is for that. I dont have 2007on this pc so I cannot give you exact steps, but I think you are on right track
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Mar 29, 2011 at 06:24 AM
Mar 29, 2011 at 06:24 AM
hi Micah,
it is in New Rule Dailogue,
Click on "Use a Formula to determine which cells to format"
and put the formula in " Format values where this formula is true"
it is in New Rule Dailogue,
Click on "Use a Formula to determine which cells to format"
and put the formula in " Format values where this formula is true"
