Taking average values of cells in a column conditional on a cell in another row having a particular value

[inferno657]

Essentially, I would like to take the mean average of the values in the 'incurred' column, conditional on those cells being part of a row that has the value 'Manual' in the other column. See screenshot for reference.

What I've tried to do is the following:

=AVERAGE(FILTER(H2:I100001,AI1:AI100001))

Unfortunately, this returned the 'VALUE!' error.

Any ideas?

 

[inferno657]

The Average Function ignores text so the cells with NA are not causing the problem.
Whilst using isnumber may get round the problem, are you sure that you are getting the correct answer? Especially if you have formulae in there that return an error.

I mean, I have no way to verify what the correct answer actually is- but the result returned appears to be consistent with what I'd expect, at least.

 

[SanjayGMusafir]

The Average Function ignores text so the cells with NA are not causing the problem.
Whilst using isnumber may get round the problem, are you sure that you are getting the correct answer? Especially if you have formulae in there that return an error.

Fluff with my data set it's working fine and giving correct value. If you may suggest some way to verify.

 

[Fluff]

Impossible to verify without knowing why there are #value errors in one or more of those cells.

 

[SanjayGMusafir]

Impossible to verify without knowing why there are #value errors in one or more of those cells.

Fluff, I have learned so much from you and you have been helping me over the years in this forum -

I try to explain...

When I tried even filtering two columns together, it was giving error, so ultimately it has to give error on the Average function too. The moment I changed it to one column, just to check if there was any error in formula population, it worked. Then I added average function to it - It was bound to work.

Just to verify the result as you suggested, I pulled out the data in seperate column and put it in Average formula - The results were exactly same.

All your suggestions and inputs are welcome, because it enhances my knowledge.

Regards

 

[Fluff]

This formula that you posted

=AVERAGE(FILTER(H2:I100001,AI2:AI100001="Manual"))

should work quite happily & the only way I know for it to return a #value error, is if that error exists in col H or I

 

[inferno657]

So the formula just used here...if I want to use it the same way but for a different column and only for numerical values within a given range i.e ages 17-24, 25-39 etc...how would I do this?

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks