Average only positive numbers in range

**classiquebabe** · 07-08-2011, 10:16 AM

Hi, I am trying to create a formula that basically says if any numbers in the 12 rows specified are 0 or greater, then add them all together and find the average. however if any of them are negative numbers I only want to add the positive numbers together and find the average of the positive numbers. How do I do this formula? I started the formula like this: if(D8:D19>0,[Sum(D8:D19)/12], [], but have NO clue how to make the false statement take only the positive numbers and average them only. Also I wasn't sure if it would be easier if I added an additional column so if the number was negative then it was in column E. Please help

**jeffreybrown** · 07-08-2011, 11:37 AM

This should do it...

=AVERAGEIF(D8:D19,">0",D8:D19)

**classiquebabe** · 07-08-2011, 12:15 PM

This does not give me the correct number. also there are 2 other issues with it, when there are no numbers in the form or all numbers are negative I get this error #DIV/0!
I founnd this other formula in google, and it works, but the same errors =SUMIF(F8:F21,">0",F8:F21)/COUNTIF(F8:F21,">0")

**jeffreybrown** · 07-08-2011, 12:27 PM

Do you have sample data you can attach?

You could wrap the function with Iferror but you still should not get this error.

**classiquebabe** · 07-08-2011, 01:32 PM

C D E F
9 Tank Bergan Portable Difference
10 #1 Port 56 59.4 3.4
11 #1 Stbd 55 58.2 3.2
12 #2 Port 58 61.1 3.1
13 #2 Stbd 56 60.9 4.9
14 #3 Port 58 61.1 3.1
15 #3 Stbd 58 60.9 2.9
16 #4 Port 57 60.1 3.1
17 #4 Stbd 56 58.6 2.6
18 #5 Port 56 59.7 3.7
19 #5 Stbd 58 61.2 3.2
20 #6 Port 60 62.8 2.8
21 #6 Stbd 60 62.9 2.9

THE ABOVE IS MY TABLE (I HAVE IDENTIFIED THE COLUMNS AND ROW NUMBERS

I AM USING THE FORMULA YOU SUGESTED =AVERAGEIF(F10:F21,">0",F10:F21)
I THINK I MADE AN ERROR PASTING IT, NOW THE VALUE IS CORRECT, BUT WHEN I DELETE THE NUMBERS OR HAVE ALL NEGATIVES THE FORMULA GIVES THE ERROR #DIV/0!
STILL. - REALLY APPRECIEATE THE HELP