How to Average Negative and Positive Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

Averaging is a common task in the workplace like data analysis. Excel gives us intriguing tools and formulae when calculating the average of data. Do you ever need to compute averages in Excel? Then buckle up because we’ll take you on a journey to show you how to average negative and positive numbers in excel in this post. Stay with us and have fun while you’re at it.


Download Practice Workbook

You can download the practice workbook from the following download button.


3 Methods to Average Negative and Positive Numbers in Excel

Averaging a collection of data that contains positive and negative numbers depends wholly upon the requirement of the Excel user’s job. So, for that reason, we have created a column in Excel with positive and negative numbers like Water Height from the Average Depth of a River in columns C4:C11. Also, we have a Date column in column B4:B11 and a Flooding Condition column in column D4:D11 related to the water height of that particular river.

Sample Data: How to AVERAGE Negative and Positive Numbers in Excel


1. Use AVERAGE Function to Average Negative and Positive Numbers

The AVERAGE function is the statistical function in Excel that returns the average value of data. In the following example, we will average data in two procedures.

📌 Steps:

  • First of all, the AVERAGE Change in Water Height is found by using the following formula in Excel:

=AVERAGE(C5:C13)

Here,

  • Cells C5:C13 has positive and negative numbers.
  • The AVERAGE function will add the positive and negative numbers and divide them by the available numbers of water height data in this case the number is 9.
  • After that, we used a formula to find the average using the ABS function. We have used the following formula:
=AVERAGE(ABS(C5:C13))

Here,

  • Cells C5:C13 has positive and negative numbers.
  • ABS (C5:C13) returns {2;13;22;5;25;1.7;1;11;3} that means ABS turns all negative value into positive value.
  • Now, these positive values {2;13;22;5;25;1.7;1;11;3} are the input of the AVERAGE function that will add the numbers and divide them by the available numbers of water height data in this case the number is 9.

Using AVERAGE function to average negative and positive numbers in Excel

Read More: How to Calculate Average Numbers in Excel (9 Handy Methods)


2. Using AVERAGE and IF Functions

Now we will use the statistical function called the AVERAGE function in Excel which returns the average value of data with the combination of the IF function. This procedure is necessary when we don’t need the overall average but an average of particular data.

📌 Steps:

  • So, for now, we need the water height data when the Flooding Condition is Normal. Now we will utilize the following formula in Excel:

=AVERAGE(IF(D5:D13="Normal",ABS(C5:C13)))

Here,

  • Cells C5:C13 has positive and negative numbers.
  • IF (D5:D13=”Normal”, then return ABS (C5:C13)) which will be {2; FALSE; FALSE;5; FALSE;1.7;1; FALSE;3}
  • Now, the AVERAGE function will add the numbers and divide them by the available numbers of water height data in this case the number is 5.

Applying AVERAGE and IF Function

📌 Steps:

  • So, for now, we need the water height data when the Flooding Condition is flood and the corresponding water height is also greater than 20. Now we will utilize the following formula in Excel:

=AVERAGE(IF(D5:D13="Flood",IF(C5:C13>20,C5:C13)))

Here,

  • Cells C5:C13 has positive and negative numbers.
  • IF (C5:C13>20, C5:C13) which will be {FALSE; FALSE;22; FALSE;25; FALSE; FALSE; FALSE; FALSE}
  • IF (D5:D13=”Flood”, IF (C5:C13>20, C5:C13)) will be the input of AVERAGE function which will look like the following formula:  =AVERAGE ({FALSE; FALSE;22; FALSE;25; FALSE; FALSE; FALSE; FALSE})
  • Now, the AVERAGE function will add the numbers and divide them by the available numbers of water height data in this case the number is 2.

Applying AVERAGE and IF Function for conditional averaging data

Read More: How to Average Values Greater Than Zero in Excel (4 Ways)


Similar Readings


3. Using AVERAGEIF Function

In the previous method, we used the statistical function called AVERAGE function in Excel which returns the average value of data with the combination of the IF function but Excel has a built-in function that can do the same job more easily and spontaneously. In the following procedure, we will use the AVERAGEIF function to find the average with conditions.

📌 Steps:

  • So, for now, we need the water height data when the Flooding Condition is Flood and the corresponding water height is also greater than 20. Now we will utilize the following formula in Excel:

=AVERAGEIF(C5:C13,">-3")

Here,

  • AVERAGEIF(range, criteria, [average_range])
  • range=C5:C13
  • criteria=“>-3”
  • Now, the AVERAGEIF function will add the numbers and divide them by the available numbers of water height data.

Applying AVERAGEIF Function

Read More: How to Calculate Average True Range in Excel (with Easy Steps)


Conclusion

Follow these steps and stages on how to average negative and positive numbers in Excel. You are welcome to download the workbook and use it for your practice. If you have any questions, concerns, or suggestions, please leave them in the comments section in the comments box of our blog ExcelDemy.


Related Articles

Al Ikram Amit
Al Ikram Amit

Hello, I'm Amit, a BUET graduate with a passion for Excel. Currently, I work as an Excel & VBA Content Developer at ExcelDemy, contributing insightful articles to the blog. I enjoy sharing my knowledge with others and always look for opportunities to grow as an Excel expert. Feel free to reach out if you need assistance or want to discuss the latest trends in Excel. Let's excel together in the world of data manipulation and analysis!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo