Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
I am not sure how to set up the conditional formatting for the following:
I have a group of employees who work with patient accts. So, in cell B2, B9, B16, B23, B30, B37 and B44 is the number of patients that each employee worked with that day. In cell B51 I have it set up to average all of the numbers in those cells just listed. What I want to do is set up a conditional format so if they are within five above or five below that average, their cell turns one color. If they are more than five below the average the cell turns a different color and if more than five above the average it turnes a different color. That way they can see at a glance if they are working within the average. Can someone help?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
Select the cells (beginning with B2) and use the following formula for
the first condition : =AND(B2<$B$51-5,B2$B$51+5) use the following formula for the second condition : =OR(B2<$B$51-5,B2$B$51+5) HTH Daniel I am not sure how to set up the conditional formatting for the following: I have a group of employees who work with patient accts. So, in cell B2, B9, B16, B23, B30, B37 and B44 is the number of patients that each employee worked with that day. In cell B51 I have it set up to average all of the numbers in those cells just listed. What I want to do is set up a conditional format so if they are within five above or five below that average, their cell turns one color. If they are more than five below the average the cell turns a different color and if more than five above the average it turnes a different color. That way they can see at a glance if they are working within the average. Can someone help?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
You can put any formula you have on the worksheet into a conditional format.
Instead of putting B51 in the conditional format you could use average in the conditional formating =AND(B2<Average(B2, B9, B16, B23, B30, B37, B44)-5,B2Average(B2, B9, B16, B23, B30, B37, B44)+5) "erregsec" wrote: I am not sure how to set up the conditional formatting for the following: I have a group of employees who work with patient accts. So, in cell B2, B9, B16, B23, B30, B37 and B44 is the number of patients that each employee worked with that day. In cell B51 I have it set up to average all of the numbers in those cells just listed. What I want to do is set up a conditional format so if they are within five above or five below that average, their cell turns one color. If they are more than five below the average the cell turns a different color and if more than five above the average it turnes a different color. That way they can see at a glance if they are working within the average. Can someone help?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
I believe your AND and OR should be switched around.
-- future_vba_expert "Daniel.C" wrote: Select the cells (beginning with B2) and use the following formula for the first condition : =AND(B2<$B$51-5,B2$B$51+5) use the following formula for the second condition : =OR(B2<$B$51-5,B2$B$51+5) HTH Daniel I am not sure how to set up the conditional formatting for the following: I have a group of employees who work with patient accts. So, in cell B2, B9, B16, B23, B30, B37 and B44 is the number of patients that each employee worked with that day. In cell B51 I have it set up to average all of the numbers in those cells just listed. What I want to do is set up a conditional format so if they are within five above or five below that average, their cell turns one color. If they are more than five below the average the cell turns a different color and if more than five above the average it turnes a different color. That way they can see at a glance if they are working within the average. Can someone help?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
You quite right !
Within range : =AND(B2=$B$51-5,B2<=$B$51+5) Outside : =OR(B2<$B$51-5,B2$B$51+5) Daniel I believe your AND and OR should be switched around. -- future_vba_expert "Daniel.C" wrote: Select the cells (beginning with B2) and use the following formula for the first condition : =AND(B2<$B$51-5,B2$B$51+5) use the following formula for the second condition : =OR(B2<$B$51-5,B2$B$51+5) HTH Daniel I am not sure how to set up the conditional formatting for the following: I have a group of employees who work with patient accts. So, in cell B2, B9, B16, B23, B30, B37 and B44 is the number of patients that each employee worked with that day. In cell B51 I have it set up to average all of the numbers in those cells just listed. What I want to do is set up a conditional format so if they are within five above or five below that average, their cell turns one color. If they are more than five below the average the cell turns a different color and if more than five above the average it turnes a different color. That way they can see at a glance if they are working within the average. Can someone help?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |