Average only other column's non blank, negative or positive values

I have a sheet that has a few columns. The pertinent two are Total Hours and Value
There are blank lines within the range.
I wish to average Total hours without counting blanks in the Value  column and only when the value column is negative.
I also wish to average Total hours without counting blanks in the Value  column and only when the value column is positive.

(see enclosed)
I know how to average ignoring blank lines with Averageif() but the other column reference as well as the negative  (or positive) stumps me.
Sample-Avg-question.xlsx
GNOVAKAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
To average positives....

=AVERAGEIFS(A:A,A:A, "<>",E:E, ">0")

To average negatives...

=AVERAGEIFS(A:A,A:A, "<>",E:E, "<0")
0
 
GNOVAKAuthor Commented:
Excellent - thanks!

Question - is there a similar method for Min() MAX()?  Cant seem to see any
0
 
Wayne Taylor (webtubbs)Commented:
I wish there was a MINIFS and MAXIFS, but unfortunately there isn't. You can use an array formula though...

=MIN(IF(A:A="", E:E))

Instead of just pressing Enter after typing the formula, use Ctrl+Shift+Enter and curly brackets should wrap the formula.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.