AVERAGE<\/strong> function alone will not accomplish this task.<\/p>\n\n\n\nIn this tutorial we will look at 4 methods we can use to find the average of positive or negative numbers of a dataset in Excel.<\/p>\n\n\n\n
Method 1: The AVERAGE and IF functions<\/h2>\n\n\n\n We can use a formula that combines AVERAGE and IF functions <\/strong>to do this task.<\/p>\n\n\n\nWe will use the following dataset to show how this can be done:<\/p>\n\n\n\n <\/figure>\n\n\n\nCalculate the average of only the positive numbers<\/h3>\n\n\n\n To calculate the average of only the positive numbers of the dataset, we do the following:<\/p>\n\n\n\n
\nSelect cell A7<\/strong> and key in the formula<\/li>\n<\/ol>\n\n\n\n=AVERAGE(IF(A2:A6>0,A2:A6,””))<\/p>\n\n\n\n <\/figure>\n\n\n\n\nSince this is an array formula, press Ctrl + Shift + Enter<\/strong> to enter the formula. In Excel 365<\/strong> we press only the Enter key to load the result.:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nWhen we look in the formula bar, we notice that Excel has put the formula in curly brackets<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nThe curly brackets<\/strong> are how Excel recognizes an array formula<\/strong>. They cannot be entered manually; they can only be produced when we press Ctrl + Shift + Enter<\/strong>.<\/p>\n\n\n\nExplanation of the formula<\/strong><\/p>\n\n\n\n=AVERAGE(IF(A2:A6>0,A2:A6,\"\"))<\/code><\/pre>\n\n\n\n\nIF(A2:A6>0, A2:A6,””)<\/em>. The IF<\/strong> function checks whether the condition A2:A6>0<\/em> is met and returns positive values if it is met and empty strings if it is not met. This results in the array {45,<\/strong> “”,””,””,78}<\/strong>.<\/li>\n\n\n\nThe formula then becomes =AVERAGE({45, “”,””,””,78})<\/em> and the average or arithmetic mean value of 61.5<\/strong> is returned.<\/li>\n<\/ul>\n\n\n\nCalculate the average of only the negative numbers<\/h3>\n\n\n\n To calculate the average of only the negative numbers, we do the following:<\/p>\n\n\n\n
\nSelect cell A7<\/strong> and type in the formula =AVERAGE(IF(A2:A6<0,A2:A6,””))<\/em>.<\/li>\n\n\n\nSince this is an array formula, press Ctrl + Shift + Enter<\/strong> to enter the formula. In Excel 365, we press only the Enter<\/strong> key to load the result.<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nExplanation of the formula <\/strong><\/p>\n\n\n\n=AVERAGE(IF(A2:A6<0,A2:A6,””))<\/em><\/p>\n\n\n\n\nIF(A2:A6<0,A2:A6,””)<\/em>. The IF<\/strong> function checks whether the condition A2:A6<0<\/em> is met and returns negative values if it is met and empty strings if it is not met. This results in the array {<\/strong>“”,<\/strong>-56,-98,-56, “”}<\/strong>.<\/li>\n\n\n\nThe formula then becomes =AVERAGE({“”,-56,-98,-56, “”}),<\/em> and the value -70<\/strong> is returned.<\/li>\n<\/ul>\n\n\n\nMethod 2: The AVERAGEIF function<\/h2>\n\n\n\n We can use the AVERAGEIF<\/strong> function to calculate the average of positive or negative numbers in a data range.<\/p>\n\n\n\nThe AVERAGEIF <\/strong>function finds the average or the arithmetic mean for the cells specified by a given condition or criteria.<\/p>\n\n\n\nCalculate the average of only the positive numbers<\/h3>\n\n\n\n To calculate the average of only the positive numbers in the data range, we do the following:<\/p>\n\n\n\n
\nSelect cell A7<\/strong> in our dataset and key in the formula:<\/li>\n<\/ol>\n\n\n\n=AVERAGEIF(A2:A6,\">0\")<\/code><\/pre>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter<\/strong> key:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nCalculate the average of only the negative numbers<\/h3>\n\n\n\n To calculate the average of only the negative numbers of the dataset we do the following:<\/p>\n\n\n\n
\nSelect cell A7<\/strong> in our dataset and key in the formula:<\/li>\n<\/ol>\n\n\n\n=AVERAGEIF(A2:A6,\"<0\")<\/code><\/pre>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter<\/strong> key:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nMethod 3: The SUMIF and COUNTIF functions<\/h2>\n\n\n\n We can use the combination of SUMIF<\/strong> and COUNTIF<\/strong> functions to get the average of positive or negative numbers in a dataset.<\/p>\n\n\n\nThe SUMIF<\/strong> function adds the cells specified by a given condition or criteria.<\/p>\n\n\n\nThe COUNTIF<\/strong> function counts the number of cells within a range that meet the given condition.<\/p>\n\n\n\nCalculate the average of only the positive numbers<\/h3>\n\n\n\n We use the following steps:<\/p>\n\n\n\n
\nSelect cell A7<\/strong> and type in the formula:<\/li>\n<\/ol>\n\n\n\n=SUMIF(A2:A6,\">0\")\/COUNTIF(A2:A6,\">0\")<\/code><\/pre>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter<\/strong> key to load the result:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nCalculate the average of only the negative numbers<\/h3>\n\n\n\n To calculate the average of only the negative values, we use the same steps as before, except in cell A7<\/strong> we type in the formula:<\/p>\n\n\n\n=SUMIF(A2:A6,\"<0\")\/COUNTIF(A2:A6,\"<0\")<\/code><\/pre>\n\n\n\nWe then press the Enter<\/strong> key to load the result:<\/p>\n\n\n\n <\/figure>\n\n\n\nMethod 4: AutoFilter and SUBTOTAL function<\/h2>\n\n\n\n We can use AutoFilter<\/strong> and the SUBTOTAL<\/strong> function to compute the average of positive or negative numbers in a dataset.<\/p>\n\n\n\nCalculate the average of only the positive numbers<\/h3>\n\n\n\n To apply this method to calculate the average of only positive numbers, we use the following steps:<\/p>\n\n\n\n
\nSelect any cell in the data range and press Ctrl + Shift + L<\/strong> or click Data >> Sort & Filter >> Filter <\/strong>to apply AutoFilter<\/strong>:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nClick on the button with the down arrow in the column header row:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nOn the shortcut menu select Number Filters<\/strong> and then select the Greater Than<\/strong> comparison:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nEnter the filter criteria and click OK<\/strong>:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nClick Home >> Editing >> AutoSum Arrow >> Average<\/strong>:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nCheck the range of cells to be averaged and correct the range address as necessary:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter<\/strong> key to load the result:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nCalculate the average of only the negative numbers<\/h3>\n\n\n\n To calculate the average of only the negative numbers, we do the following:<\/p>\n\n\n\n
\nSelect any cell in the data range and press Ctrl + Shift + L<\/strong> or click Data >> Sort & Filter >> Filter <\/strong>to apply AutoFilter.<\/strong><\/li>\n\n\n\nClick on the button with the down arrow in the column header row.<\/li>\n\n\n\n On the shortcut menu select Number Filters<\/strong> and then select the Less Than<\/strong> comparison:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nEnter the filter criteria and click OK:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nClick Home >> Editing >> AutoSum Arrow >> Average<\/strong>:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nCheck the range of cells to be averaged and correct the range address as necessary:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter<\/strong> key to load the result:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nAdditional information on the SUBTOTAL function<\/h3>\n\n\n\n The SUBTOTAL<\/strong> function returns a subtotal in a list or a database.<\/p>\n\n\n\nIf we want to use the SUBTOTAL<\/strong> function in a cell other than the one immediately below the filtered list, we can type in the formula instead of using the AutoSum<\/strong> function.<\/p>\n\n\n\nThe first argument in the SUBTOTAL<\/strong> function is a function number that specifies how the subtotal should be calculated. The numbers for specifying the average are 1<\/strong> or 101<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n In this tutorial, we have explored 4 methods that we can use to find the average of positive or negative numbers of a dataset in Excel.<\/p>\n\n\n\n
The methods are the use of the AVERAGE<\/strong> and IF<\/strong> functions, applying the AVERAGEIF <\/strong>function, the use of the SUMIF<\/strong> and COUNTIF<\/strong> functions, and the use of the AutoFilter<\/strong> feature and the SUBTOTAL<\/strong> function.<\/p>\n","protected":false},"excerpt":{"rendered":"The Excel AVERAGE function returns the average (arithmetic mean) of its arguments. Sometimes we may want to find out the average of only…<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170,190],"yoast_head":"\n
Average Positive or Negative Numbers in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n