Averaging is a common task in the workplace like data analysis. Excel gives us lots of tools and functions 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.
How to Average Negative and Positive Numbers in Excel: 3 Methods
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 the range C4:C11. Also, we have a Date column in range B4:B11 and a Flooding Condition column in range 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.
=AVERAGE(C5:C13)- Range 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))- Range C5:C13 has positive and negative numbers.
- ABS (C5:C13) returns {2;13;22;5;25;1.7;1;11;3} which means ABS turns all negative values into positive values.
- 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 Average Filtered Data in Excel
2. Using AVERAGE and IF Functions
Now we will use the AVERAGE function 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. We will use the following formula in Excel.
=AVERAGE(IF(D5:D13="Normal",ABS(C5:C13)))Here,
- Range 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.
- We need the water height data when the Flooding Condition is flooded and the corresponding water height is also greater than 20. Now we will use the following formula in Excel.
=AVERAGE(IF(D5:D13="Flood",IF(C5:C13>20,C5:C13)))Here,
- Range 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 the 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.
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. 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:
- We need the water height data when the Flooding Condition is flooded and the corresponding water height is also greater than 20. Now we will apply the following formula.
=AVERAGEIF(C5:C13,">-3")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 of Text in Excel
Download Practice Workbook
You can download the practice workbook from the following download button.
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.
Related Articles
- How to Do Subtotal Average in Excel
- How to Calculate Sum & Average with Excel Formula
- How to Calculate Average and Standard Deviation in Excel
- How to Calculate Average Deviation in Excel Formula
- How to Calculate Average Excluding Outliers in Excel
- How to Calculate Average of Top 5 Values in Excel
- How to Calculate Average from Different Sheets in Excel
<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
