Average if criteria met

[sharky12345]

Guys,

I've searched everywhere for the solution to this problem so hope someone can help;

I want the formula that will give me the average of Column E only provided Column B has the figure 2 in it.

Column E is numeric values only and I want the average of the values if 2 appears in column B - BUT the columns are on a seperate sheet, (called '1'), and this is what appears to be causing the issues.

Can anyone suggest a solution?

 

[Aladin Akyurek]

Guys,

I've searched everywhere for the solution to this problem so hope someone can help;

I want the formula that will give me the average of Column E only provided Column B has the figure 2 in it.

Column E is numeric values only and I want the average of the values if 2 appears in column B - BUT the columns are on a seperate sheet, (called '1'), and this is what appears to be causing the issues.

Can anyone suggest a solution?

1) Control+shift+enter, not just enter on all versions:

=AVERAGE(IF('1'!$B$2:$B$100=2,'1'!$E$2:$E$100))

2) Just enter on all versions:

=SUMIF('1'!$B$2:$B$100,2,'1'!$E$2:$E$100)/COUNTIF('1'!$B$2:$B$100,2)

3) Just enter on Excel 2007 or later:

=AVERAGEIF('1'!$B$2:$B$100,2,'1'!$E$2:$E$100)

 

[sharky12345]

That works great!

Thanks for saving me even more time trying to figure that one out!

 

[Aladin Akyurek]

That works great!

Thanks for saving me even more time trying to figure that one out!

You are welcome. Thanks for providing feedback.

 

[sharky12345]

Aladin,

I've got a problem now in that the cell where the result is displayed is showing #DIV/0!

I've looked around for the solution with no success.

So what I need is for the average to be calculated on Sheet 1, Column G, (G7:G10000), but only if '2' appears in Column B, (B7:B10000), ignoring the scenario where there are no cells that match the criteria.

Can you help?

 

[Aladin Akyurek]

Aladin,

I've got a problem now in that the cell where the result is displayed is showing #DIV/0!

I've looked around for the solution with no success.

So what I need is for the average to be calculated on Sheet 1, Column G, (G7:G10000), but only if '2' appears in Column B, (B7:B10000), ignoring the scenario where there are no cells that match the criteria.

Can you help?

Not knowing which option you picked out...

1) Control+shift+enter, not just enter on all versions:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
  AVERAGE(IF('1'!B7:B10000=2,'1'!G7:G10000))))

2) Just enter on all versions:

=SUMIF('1'!B7:B10000,2,'1'!G7:G10000)/
   MAX(1,COUNTIF('1'!B7:B10000,2))

3) Just enter on Excel 2007 or later:

=IFERROR(AVERAGEIF('1'!B7:B10000,2,'1'!G7:G10000),0)

All are modified to return 0 when #DIV/0! would show up.

 

[sharky12345]

Option 2 worked a treat - thank you!

I didn't try Option 1 but did Option 3 and that gave me a #Name? error so not sure what went wrong there.

Doesn't matter anyway - your 2nd Option worked so thanks again!

 

[Aladin Akyurek]

Option 2 worked a treat - thank you!

I didn't try Option 1 but did Option 3 and that gave me a #Name? error so not sure what went wrong there.

Doesn't matter anyway - your 2nd Option worked so thanks again!

You are welcome. Thanks for providing feedback. By the way, option 3 requires Excel 2007 or later.

 

[sharky12345]

Of course it does, as pointed out in your original reply - my fault for not reading properly!

I have lots of things to do with this workbook, (especially with Mail Merge so if you know lots about that then great!), no doubt I'll be in touch for more advice!

 

[Aladin Akyurek]

Of course it does, as pointed out in your original reply - my fault for not reading properly!

I have lots of things to do with this workbook, (especially with Mail Merge so if you know lots about that then great!), no doubt I'll be in touch for more advice!

No problem. I think there are some folks around here with knowledge about mail merge.