If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional formatting with formula
I am clearly not understanding how to use the formula in
conditional formatting. Here is what I am trying to do: Cell B10 copies the value from Sheet2!Z100. If Sheet2! Z100 is blank, then B10=Average(B7:B9). I am trying to highlight B10 if is is using an average rather than a real value. My actual formula in the cell is: =IF('Actual TS Hours'!Z200,'Actual TS Hours'!Z20,AVERAGE (B4848)) I went into conditional formatting, selected "formula is" and entered: =AVERAGE(B4848) What's happening is that ALL the cells are highlighted, whether or not they are equal to the average of the previous three cells. I tried to really force a false condition and changed the conditional formula to: =SUM(B4848) The cells STILL stayed highlighted. I have tried looking in help and can't find any guidance on this. Can anyone enlighten me? Also, is there a way to SEARCH on this newsgroup? I could not find a way, so I just paged my way through lots of pages to see if this question had already been addressed. Thanks for any help you can give me. Ann |
#2
|
|||
|
|||
Conditional formatting with formula
Use these parameters:
"Cell Value is" "Equal to" "=Average(B7:B9)" And then set your formats. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Ann Scharpf" wrote in message ... I am clearly not understanding how to use the formula in conditional formatting. Here is what I am trying to do: Cell B10 copies the value from Sheet2!Z100. If Sheet2! Z100 is blank, then B10=Average(B7:B9). I am trying to highlight B10 if is is using an average rather than a real value. My actual formula in the cell is: =IF('Actual TS Hours'!Z200,'Actual TS Hours'!Z20,AVERAGE (B4848)) I went into conditional formatting, selected "formula is" and entered: =AVERAGE(B4848) What's happening is that ALL the cells are highlighted, whether or not they are equal to the average of the previous three cells. I tried to really force a false condition and changed the conditional formula to: =SUM(B4848) The cells STILL stayed highlighted. I have tried looking in help and can't find any guidance on this. Can anyone enlighten me? Also, is there a way to SEARCH on this newsgroup? I could not find a way, so I just paged my way through lots of pages to see if this question had already been addressed. Thanks for any help you can give me. Ann |
#3
|
|||
|
|||
Conditional formatting with formula
Hi Ann,
Just to add a little insight. Whenever you want to apply conditional formatting and you need to use a formula, you can use almost any formula as long as it evaluates to either TRUE or FALSE. With your example you don't need to use a formula but you could express it as a formula like =B10=AVERAGE(B4848) This would evaluate to TRUE and apply the format. You cannot use arrays or unions in CF formulas. As for searching the archives for help, I would highly recommend this add-in from Ron deBruin. It's a free download and makes searching the archives via Google very easy and fast. Take a look: http://www.rondebruin.nl/Google.htm Biff -----Original Message----- I am clearly not understanding how to use the formula in conditional formatting. Here is what I am trying to do: Cell B10 copies the value from Sheet2!Z100. If Sheet2! Z100 is blank, then B10=Average(B7:B9). I am trying to highlight B10 if is is using an average rather than a real value. My actual formula in the cell is: =IF('Actual TS Hours'!Z200,'Actual TS Hours'!Z20,AVERAGE (B4848)) I went into conditional formatting, selected "formula is" and entered: =AVERAGE(B4848) What's happening is that ALL the cells are highlighted, whether or not they are equal to the average of the previous three cells. I tried to really force a false condition and changed the conditional formula to: =SUM(B4848) The cells STILL stayed highlighted. I have tried looking in help and can't find any guidance on this. Can anyone enlighten me? Also, is there a way to SEARCH on this newsgroup? I could not find a way, so I just paged my way through lots of pages to see if this question had already been addressed. Thanks for any help you can give me. Ann . |
#4
|
|||
|
|||
Conditional formatting with formula
Thanks to both RD and Biff for their replies! I tried
the "cell value is" method that RD outlined and it worked like a charm. And I looked at the Google add-in. I will download it to both my home & work systems! But, if I can do this with the "cell value is" - why do I need the formula option? Still don't understand how the formula works! Ann |
#5
|
|||
|
|||
Conditional formatting with formula
Hi Ann,
There are many situations where you want to use CF and the only way you can define or express the condition is through a formula. =B10=AVERAGE(B4848) Ok, think of it in these terms. Does the value in B10 equal the average of cells B4848. If the answer is YES then the conditional format is applied. If the answer is NO then the conditional format is not applied. CF is a very useful tool and has alot of flexibility due to the Formula Is option. With the Google search add-in, you will find that it is exponentially better than using XL help. Biff -----Original Message----- Thanks to both RD and Biff for their replies! I tried the "cell value is" method that RD outlined and it worked like a charm. And I looked at the Google add-in. I will download it to both my home & work systems! But, if I can do this with the "cell value is" - why do I need the formula option? Still don't understand how the formula works! Ann . |
#6
|
|||
|
|||
Conditional formatting with formula
Ann,
Try this as an example of the different parameter choices: A1 contains this formula: =IF(B1"",B1*D1,"") And since each row designates a day, you drag copy this down ColumnA for 30days to prepare for the month. You want Column A to alert you when it doesn't contain the value 100. You would like the cells to be colored yellow and the font to display in red when *anything* other then the value 100 is displayed. However, since each row designates a day, and there are as yet unfilled days, you DON'T want the EMPTY, blank cells (which don't contain 100, just the formula), to display the warning format. You would use this set-up: "Formula Is" =AND(A1100,A1"") If you used: "Cell Value Is" "Equal To" =AND(A1100,A1"") It wouldn't work ! -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Ann Scharpf" wrote in message ... Thanks to both RD and Biff for their replies! I tried the "cell value is" method that RD outlined and it worked like a charm. And I looked at the Google add-in. I will download it to both my home & work systems! But, if I can do this with the "cell value is" - why do I need the formula option? Still don't understand how the formula works! Ann |
Thread Tools | |
Display Modes | |
|
|