Average if it meets criteria problems...

craiced

New Member
Joined
Sep 18, 2006
Messages
8
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

New Member
Joined
Sep 18, 2006
Messages
8

ADVERTISEMENT

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

New Member
Joined
Sep 18, 2006
Messages
8
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

...

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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Sep 18, 2006
Messages
8
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 :biggrin:
J

PS I am sure I will be back :devilish:
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,332
Members
410,603
Latest member
rseckler
Top