<\/figure>\n\n\n\nOnce we click OK<\/strong>, our table will look like this:<\/p>\n\n\n\n <\/figure>\n\n\n\n\nThe formula in the sheet directly<\/strong><\/li>\n<\/ol>\n\n\n\nWe can also combine the IF and AVERAGE formulas<\/strong> on our sheet directly and then use the results that we get to highlight the players.<\/p>\n\n\n\nWe will put the formula in column C<\/strong>, and it will be like this:<\/p>\n\n\n\n=IF(B2<AVERAGE($B$2:$B$21), \"Less than average\", \"Higher than average\")<\/code><\/pre>\n\n\n\nWhat this formula does is that it checks if the number in cell B2<\/strong> is smaller than the average in the range B2:B21<\/strong> (these cells are locked as the range will not change) and gives out the result \u201cless than average\u201d<\/strong> if the statement is true and \u201chigher than average\u201d<\/strong> if this statement is false.<\/p>\n\n\n\nWe will drag this formula to cell C21<\/strong>. Our table looks like this:<\/p>\n\n\n\n <\/figure>\n\n\n\nLet us say that we want to find all the players that have fewer points than the average. To do this, we will select range C2:C21<\/strong> and then go to Home >> Conditional Formatting >> Highlight Cells Rules >> Text that Contains:<\/strong><\/p>\n\n\n\n <\/figure>\n\n\n\nWhen a pop-up appears, we will input any word from column C<\/strong> that is specific so that we can find players with fewer points than average. We will simply input the word \u201cless\u201d<\/strong> (it can be the lower case as well):<\/p>\n\n\n\n <\/figure>\n\n\n\nWhen we click OK<\/strong>, cells in column C<\/strong> with players that have less than average points will be highlighted in red color:<\/p>\n\n\n\n <\/figure>\n","protected":false},"excerpt":{"rendered":"We have already shown the usefulness of conditional formatting and the various occasions on which it can be used. In the example below,…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n
Use Average with Conditional Formatting in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n