| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting with average formula and variance
Hello:
Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Try
=ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
shoud be in %
=ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Hi Jacob:
It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
1. Select the Range (say H1:H100) or column H. Please note that the cell
reference H1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =ABS(H1-AVERAGE(B1:G1))<=2% 4. Click Format ButtonPattern and select your color (say Green) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hi Jacob: It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Jacob:
It worked partially. I need it to evaluate the average and if its within 2% make it green but if its outside of the 2% make it red. The formula you gave me works if the cell is less than 2% of the average but not if its more. I removed the "=" sign bcs if its 2% off of the average is still ok just anythign abover or below. I've tried to change it to the following but it doesn't work: =ABS(H27-AVERAGEA(B27:G27))<2% any ideas? Thank you Monika "Jacob Skaria" wrote: 1. Select the Range (say H1:H100) or column H. Please note that the cell reference H1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =ABS(H1-AVERAGE(B1:G1))<=2% 4. Click Format ButtonPattern and select your color (say Green) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hi Jacob: It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Jacob's formula works correctly. Your formula won't work, because you are
testing for the difference not being exactly equal to 2% What numbers do you have in which of your cells (B to H), what result did you get from Jacob's formula, & what result did you expect? As a matter of interest, why did you change from AVERAGE to AVERAGEA? Do you have non-numeric values in some of your cells? -- David Biddulph "murkaboris" wrote in message ... Jacob: It worked partially. I need it to evaluate the average and if its within 2% make it green but if its outside of the 2% make it red. The formula you gave me works if the cell is less than 2% of the average but not if its more. I removed the "=" sign bcs if its 2% off of the average is still ok just anythign abover or below. I've tried to change it to the following but it doesn't work: =ABS(H27-AVERAGEA(B27:G27))<2% any ideas? Thank you Monika "Jacob Skaria" wrote: 1. Select the Range (say H1:H100) or column H. Please note that the cell reference H1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =ABS(H1-AVERAGE(B1:G1))<=2% 4. Click Format ButtonPattern and select your color (say Green) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hi Jacob: It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Hello David:
the AVERAGEA was just a type my formula actually reads "AVERAGE" -- sorry. So using the formula given my average of a row in cells from B to G comes to 23.5% and my H cell is 28% so based on the formula it should be read bcs the difference is more than 2% from the 23.5% average but using the formula it keeps it black. Same if it is below. The only time it makes it red is if its withing those 2%. ie. if my cell H is 25% than the conditional formatting makes it red but I need it the other way around. B27 = 24% C27 = 21% D27 = 23% E27 = 22% F27 = 26% G27 = 26% H27 --- for test purposes I used 21% to test the lover range which should have changed the number to red but didn't, 28% also should be highlighted but didn't and 25% which is in the 2% range should have stayed black but that's the one that changed to red. Thanks Monika B "David Biddulph" wrote: Jacob's formula works correctly. Your formula won't work, because you are testing for the difference not being exactly equal to 2% What numbers do you have in which of your cells (B to H), what result did you get from Jacob's formula, & what result did you expect? As a matter of interest, why did you change from AVERAGE to AVERAGEA? Do you have non-numeric values in some of your cells? -- David Biddulph "murkaboris" wrote in message ... Jacob: It worked partially. I need it to evaluate the average and if its within 2% make it green but if its outside of the 2% make it red. The formula you gave me works if the cell is less than 2% of the average but not if its more. I removed the "=" sign bcs if its 2% off of the average is still ok just anythign abover or below. I've tried to change it to the following but it doesn't work: =ABS(H27-AVERAGEA(B27:G27))<2% any ideas? Thank you Monika "Jacob Skaria" wrote: 1. Select the Range (say H1:H100) or column H. Please note that the cell reference H1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =ABS(H1-AVERAGE(B1:G1))<=2% 4. Click Format ButtonPattern and select your color (say Green) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hi Jacob: It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Your condition for green is
=ABS(H27-AVERAGE(B27:G27))<=2% Your condition for red is =ABS(H27-AVERAGE(B27:G27))2% though in fact you can get away with just formatting the cell as red and then using CF for your green condition. -- David Biddulph "murkaboris" wrote in message ... Hello David: the AVERAGEA was just a type my formula actually reads "AVERAGE" -- sorry. So using the formula given my average of a row in cells from B to G comes to 23.5% and my H cell is 28% so based on the formula it should be read bcs the difference is more than 2% from the 23.5% average but using the formula it keeps it black. Same if it is below. The only time it makes it red is if its withing those 2%. ie. if my cell H is 25% than the conditional formatting makes it red but I need it the other way around. B27 = 24% C27 = 21% D27 = 23% E27 = 22% F27 = 26% G27 = 26% H27 --- for test purposes I used 21% to test the lover range which should have changed the number to red but didn't, 28% also should be highlighted but didn't and 25% which is in the 2% range should have stayed black but that's the one that changed to red. Thanks Monika B "David Biddulph" wrote: Jacob's formula works correctly. Your formula won't work, because you are testing for the difference not being exactly equal to 2% What numbers do you have in which of your cells (B to H), what result did you get from Jacob's formula, & what result did you expect? As a matter of interest, why did you change from AVERAGE to AVERAGEA? Do you have non-numeric values in some of your cells? -- David Biddulph "murkaboris" wrote in message ... Jacob: It worked partially. I need it to evaluate the average and if its within 2% make it green but if its outside of the 2% make it red. The formula you gave me works if the cell is less than 2% of the average but not if its more. I removed the "=" sign bcs if its 2% off of the average is still ok just anythign abover or below. I've tried to change it to the following but it doesn't work: =ABS(H27-AVERAGEA(B27:G27))<2% any ideas? Thank you Monika "Jacob Skaria" wrote: 1. Select the Range (say H1:H100) or column H. Please note that the cell reference H1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =ABS(H1-AVERAGE(B1:G1))<=2% 4. Click Format ButtonPattern and select your color (say Green) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hi Jacob: It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting with average formula and variance
Thank you David, this solved it.
Monika "David Biddulph" wrote: Your condition for green is =ABS(H27-AVERAGE(B27:G27))<=2% Your condition for red is =ABS(H27-AVERAGE(B27:G27))2% though in fact you can get away with just formatting the cell as red and then using CF for your green condition. -- David Biddulph "murkaboris" wrote in message ... Hello David: the AVERAGEA was just a type my formula actually reads "AVERAGE" -- sorry. So using the formula given my average of a row in cells from B to G comes to 23.5% and my H cell is 28% so based on the formula it should be read bcs the difference is more than 2% from the 23.5% average but using the formula it keeps it black. Same if it is below. The only time it makes it red is if its withing those 2%. ie. if my cell H is 25% than the conditional formatting makes it red but I need it the other way around. B27 = 24% C27 = 21% D27 = 23% E27 = 22% F27 = 26% G27 = 26% H27 --- for test purposes I used 21% to test the lover range which should have changed the number to red but didn't, 28% also should be highlighted but didn't and 25% which is in the 2% range should have stayed black but that's the one that changed to red. Thanks Monika B "David Biddulph" wrote: Jacob's formula works correctly. Your formula won't work, because you are testing for the difference not being exactly equal to 2% What numbers do you have in which of your cells (B to H), what result did you get from Jacob's formula, & what result did you expect? As a matter of interest, why did you change from AVERAGE to AVERAGEA? Do you have non-numeric values in some of your cells? -- David Biddulph "murkaboris" wrote in message ... Jacob: It worked partially. I need it to evaluate the average and if its within 2% make it green but if its outside of the 2% make it red. The formula you gave me works if the cell is less than 2% of the average but not if its more. I removed the "=" sign bcs if its 2% off of the average is still ok just anythign abover or below. I've tried to change it to the following but it doesn't work: =ABS(H27-AVERAGEA(B27:G27))<2% any ideas? Thank you Monika "Jacob Skaria" wrote: 1. Select the Range (say H1:H100) or column H. Please note that the cell reference H1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =ABS(H1-AVERAGE(B1:G1))<=2% 4. Click Format ButtonPattern and select your color (say Green) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hi Jacob: It seems to be working. I have to fully populate the rest of the xls but the test on couple of rows worked. thank you for your quick response. Monika "Jacob Skaria" wrote: shoud be in % =ABS(H7-AVERAGE(B7:G7))<=2% If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =ABS(H7-AVERAGE(B7:G7))<=2 If this post helps click Yes --------------- Jacob Skaria "murkaboris" wrote: Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Weighted Average of Variance | Excel Worksheet Functions | |||
| Conditional SUM for Budget, Actuals, & Variance YTD columns | Excel Worksheet Functions | |||
| Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
| Percent Variance Formula | Excel Discussion (Misc queries) | |||
| Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) | |||
Linear Mode
