Average if a column contains x or y

[Fazila]

I'm hoping this is an easy fix. I need a formula that will average data in a column if a column contains x or y.

Currently I have:

N22 ={IFERROR(AVERAGE(IF((SEN={"n",""}),KS2A)),"")}

However, this is returning 4.49 when it should be 4.83. It might be converting blanks to 0 hence the discrepancy.

I also need to find the gap between this figure and two other categories. The formula I am using for this is:

={IFERROR(AVERAGE(IF(ISNUMBER(MATCH(SEN,{"K","E"},0)),KS2A))-n22,"")}

Again, this is returning a lower figure - could it be converting blanks to 0 again?

Any ideas?

Thanks

Fazila

 

[James006]

Hi,

Could you describe the actual set-up of your worksheet ...

Do you have numbers or formulas producing your numbers ?

 

[Fazila]

The worksheet is quite large one sheet has the raw data in which there are these column amongst many others:

[TABLE="width: 500"]
<tbody>[TR]
[TD]SEN[/TD]
[TD]KS2A[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]4.7[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]4.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]4.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5.3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula goes into another sheet called Group analysis.

On this sheet I have the following table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]KS2 APS (average)[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]=IFERROR(AVERAGEIF(SEN,$B19,KS2A),"")[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]=IFERROR(AVERAGEIF(SEN,$B21,KS2A),"")[/TD]
[/TR]
[TR]
[TD]No SEN[/TD]
[TD]=IFERROR(SUM(COUNTIFS(SEN,{"n",""},Basics5,"Y"))/$D22,"")[/TD]
[/TR]
[TR]
[TD]Gap[/TD]
[TD]No SEN - K and E[/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes things a little more clearer?

Thanks for helping James006

 

[James006]

Hi again,

From your description ... regarding your two first formulas ...

assuming SEN and KS2A are your two named Ranges ( with identical size ) ...

As long as cell $B19 contains K and cell $B21 contains E ... both your Array formulas will produce accurate results ...

 

[Fazila]

That's the thing it doesn't - for another similar requirement the formula works perfectly but for this one it doesn't.

Could the fact that there are blank cells affect the outcome - could the formula be converting the blanks to a 0 which could affect the average?

 

[James006]

That's the thing it doesn't - for another similar requirement the formula works perfectly but for this one it doesn't.

Could the fact that there are blank cells affect the outcome - could the formula be converting the blanks to a 0 which could affect the average?

The blank cells are excluded from your Array formula ... so they do not interfere with the calculation ...

There is somewhere ... another reason for your discrepancy ...

For example ... Are you 100% sure the letter K is ' clean ' with no spaces before or after the letter ...

 

[Fazila]

Nope double checked no spaces before or after. Think I'll just create a helper column with a Y wherever there is a K or an E as I need to finish this off today but thank you again James006 for all your help.