LinkBack URL
About LinkBacks
Hello. How do I create a conditional formatting for a set of rows with %'s to turn red at the 50% mark? Spreadhsheet attached.
Hello. How do I create a conditional formatting for a set of rows with %'s to turn red at the 50% mark? Spreadhsheet attached.
50% of what ?
you have 9.6%, 7.0 and 6.5% red I5, K7 & L6
BUT not all the values that are the same as those in the rest of the spreadsheet
for example I6 & I7
would you provide a more detailed explanation please
Last edited by etaf; 05-11-2015 at 04:31 PM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
OK, here you go...
=AND(SUM($C5:C5)>=0.5,SUM($B5:B5)<=0.5)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@fdibbins. That works. Thanks. How does it work?
This will color all cells where the sum of the row is >= 50%...
=SUM($C5:C5)>=0.5
I will work on CF for just that cell
Select C5:O7
Conditional Formatting, New Rule, Use Formula
Formula:
=C5=MEDIAN($C5:$O5)
Select the formatting of your choice.
You can check this by copying the rows and sort in order and then using the MEDIAN function on each row. The same value should be chosen as is highlighted by the Conditional formatting.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
@newdoverman. That works. Thanks. Why does it work? How does it know to do that for each row? Also, what if I wanted to change the %'s?
MEDIAN finds the MIDDLE value of a range of values. If you sort the values in order, median will give the value in the middle of the list as close as can be calculated. If there is an odd number of values the middle value is one of the values in the range. If there is an even number of values the value returned is an average of the two values closest to the middle.
You can change the values and the value that would be in the middle of the sorted list will be the value that MEDIAN returns.
=AND(SUM($C5:C5)>=0.5,SUM($B5:B5)<=0.5)
the 1st part tests a running total from column C, to see when it is >=0.5
the 2nd part does the same, but is offset 1 column to the left - this is what stops the CF from keep on going
Thanks for the rep
Last edited by FDibbins; 05-11-2015 at 05:48 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks