How to Average Negative and Positive Numbers in Excel

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. 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.

Method 1 – Use the AVERAGE Function to Average Negative and Positive Numbers

Steps:

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.

We used the following formula to find the average using the ABS function:

=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.
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 number of water height data points.

Method 2 – Using the AVERAGE and IF Functions

Steps:

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)))

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}.
The AVERAGE function will add the numbers and divide them by the available numbers of water height data points.

We need the water height data when the Flooding Condition is flooded and the corresponding water height is also greater than 20. We will use the following formula in Excel.

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

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}).
The AVERAGE function will add the numbers and divide them the applicable number of data points.

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.

Alternative Steps:

We need the water height data when the Flooding Condition is flooded and the corresponding water height is also greater than 20. Apply the following formula.

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

AVERAGEIF(range, criteria, [average_range])
range = C5:C13
criteria = “>-3”