Hi
I am looking for a formula that will fill color black the rows that do not meet the average value, i have attached a sheet that shows J9 is the weekly average, above that is the persons weekly total. Any total that does not meet J9 score, the the complete row is to be fill color black.
Thanks
use the formula
=$J3<$J$9
and apply to the range =$A$2:$J$7
just need to understand what you mean by
do you mean less than or does it have to be exactly the same valueAny total that does not meet J9 score,
I have attached a conditional formatted example- it colours grey - but you can change the format to be black
for 2007 or 2010 excel version
Conditional Formatting
Highlight applicable range >>
$A$2:$J$7
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$J3<$J$9
Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
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.
Select A2:J7 and then apply conditional formatting with a formula of =ROUND(J2,0)<>ROUND($J$9,0) and a fill colour of your choice.
average%20elimination(1).xlsx
You need formula for COnditional Formatiing:
=$J2<>ROUND($J$9,0)
Then copy the format to other cells
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Just getting my head around this but the formula is, if less than J9 then fill color complete row across sheet as i will have another table to the right of this table, so i am only working with rows that are true in the first table.
not complete row - only the apply to columns
$A$2:$J$7
just change the apply to columns to which every column you want to cover
$A$2:$XFD$7
if 2010 version last column is XFD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks