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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
=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.
ASKER
Question - is there a similar method for Min() MAX()? Cant seem to see any