Al Ikram Amit
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.
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.
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.
📌 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.
Read More: How to Average Values Greater Than Zero in Excel (4 Ways)
Similar Readings
- How to Average Every Nth Row in Excel (3 Ways)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- How to Calculate Average of Averages in Excel (with Easy Steps)
- Calculate Moving Average for Dynamic Range in Excel (3 Examples)
- How to Calculate Average of Multiple Columns in Excel (6 Methods)
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.
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
- How to Calculate Daily Average in Excel (3 Easy Ways)
- Calculate Sum & Average with Excel Formula
- How to Ignore #N/A Error When Getting Average in Excel
- Calculate Average from Different Sheets in Excel
- How to Find Average of Specific Cells in Excel (3 Handy Ways)
- Calculate Percentage above Average in Excel (3 Easy Ways)
