Average if it meets criteria problems...

[craiced]

Hi,

I was hoping someone can help me with this. I have been struggling for days and cannot come up with a solution. I have searched through previous answered questions and have only confirmed that I think my formula looks correct but it still wont work out.

AVERAGE(IF(($BA17:$BA321<=-3,9)*($BA17:$BA321>=-5,6),BO17:BO321))

CSE

Can anyone out there see what is wrong with the formula. I think it is pretty eay to see what I want. The average of all numbers in Column BO whose corresponding row in BA is between -3,9 and 5,6

Can anyone help?

Thanks
John

 

[Aladin Akyurek]

=AVERAGE(IF($BA$17:$BA$321 >= A2, IF($BA$17:$BA$321 <= B2, $BO$17:$BO$321)))

where A2 <= B2 and A2:B2 houses the limiting values.

Needs control+shift+enter.

 

[NBVC]

Are you using the comma as a decimal (european)?

If so, then try:

=AVERAGE(IF($BA17:$BA321<>"";IF($BA17:$BA321>=-3,9;IF($BA17:$BA321<=5,6;$BO17:$BO321))))

Confirmed with CTRL+SHIFT+ENTER not just ENTER


Edit: Forgot to change 5.9 to 5,9....

 

[pgc01]

Hi craiced
Welcome to the board

Also, you have a typo, you say in your post that you want average of all numbers in Column BQ and in the formula you have BO.

HTH
PGC

 

[craiced]

Hi Guys,

Thanks for the quick replies!!

I am still having a couple problems with the formula though.
Aladin the formula you gave is is me an answer that is very slightly wrong in each. It sounds stupid I know, but I have put on filters copied, pasted and then worked the average that way to check. For the life of me I cant figure out why, I get 1.07784 from the formula and 1.08433 the old fashioned copy paste and average. Any ideas?

NVBC I cant get you formula to work at all, it errors and focuses on the "" after the <>. What are the "" for? It looks good but I cant figure it out. Oh yep I am using the European system

Really guys I thank you for the help because this is driving me nuts!

 

[craiced]

Hi craiced
Welcome to the board

Also, you have a typo, you say in your post that you want average of all numbers in Column BQ and in the formula you have BO.

HTH
PGC

Thanks mate I changed it

 

[Aladin Akyurek]

...

I am still having a couple problems with the formula though.
Aladin the formula you gave is is me an answer that is very slightly wrong in each. It sounds stupid I know, but I have put on filters copied, pasted and then worked the average that way to check. For the life of me I cant figure out why, I get 1.07784 from the formula and 1.08433 the old fashioned copy paste and average. Any ideas?
...

Are you using the filter Is Greater Than or Equal To AND'ed with the filter Is Less Than or Equal To in AutoFilter as does the formula?

 

[NBVC]

Hi Guys,

Thanks for the quick replies!!

I am still having a couple problems with the formula though.
...


NVBC I cant get you formula to work at all, it errors and focuses on the "" after the <>. What are the "" for? It looks good but I cant figure it out. Oh yep I am using the European system

Really guys I thank you for the help because this is driving me nuts!

If you're using the European system, shouldn't the argument separators be semi-colons instead of commas?

The formula I supplied works with commas as decimals and semi-colons as argument separators.

Try changing the decimals to periods (dots) and argument separators to commas....does that work?

 

[craiced]

Hi,

Guys first let me say thank you each of you because you really saved me from a nervous breakdown and it has been my own stupidity that has kept this going on so long tonight.

The differnce between the results from the formula and the copy pasted results was that the formula was averaging 180 through 166 cells and the filtered was doing 180 through 167.

Average in the formula was not including a single blank cell in the division while the filtered results were including it, hence the very slight difference. I am not ashamed to say there was screaming when I realised I had missed that.

Thank you and problem solved
J

PS I am sure I will be back