# Average if it meets criteria problems...

#### craiced

##### New Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
=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.

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

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

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!

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

...

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?

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?

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

Replies
5
Views
223
Replies
7
Views
188
Replies
5
Views
574
Replies
4
Views
321
Replies
5
Views
386

1,219,918
Messages
6,150,960
Members
450,996
Latest member
darko1515s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back