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?

 

[SanjayGMusafir]

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?

Try this

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

I wonder H2:I100001 will work

 

[inferno657]

Try this

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

I wonder H2:I100001 will work

It's still giving me the same type of error- I'm a little stuck

 

[SanjayGMusafir]

It's still giving me the same type of error- I'm a little stuck

Oh Yes, It's bound to give errors - See Column H you have got so many NA for Manual - How will system average those and thus give error

Have to think some way to bye-pass those NA, I think over and revert

 

[SanjayGMusafir]

Oh Yes, It's bound to give errors - See Column H you have got so many NA for Manual - How will system average those and thus give error

Have to think some way to bye-pass those NA, I think over and revert

See you have to have patience, it has become Trial-Error thing now

Try this

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

 

[Fluff]

Check that you don't have any #VALUE! errors in columns H & I.

 

[inferno657]

See you have to have patience, it has become Trial-Error thing now

Try this

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

Well I'm glad I have you here, that's all I can say!

See you have to have patience, it has become Trial-Error thing now

Try this

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

It was actually only column H I needed to filter- column I was a mistake that somehow entered the formula when I typed it, sorry. I put it into H only, and it seems to work! Thank you very much

 

[SanjayGMusafir]

Well I'm glad I have you here, that's all I can say!

It was actually only column H I needed to filter- column I was a mistake that somehow entered the formula when I typed it, sorry. I put it into H only, and it seems to work! Thank you very much

Yes with single column it's working perfectly. I also checked with a sample data

 

[Fluff]

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.

 

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

Let me check on that and I revert back