Averageif query

[fable]

Hey guys,

Just looking for a quick bit of help.

Looking to use averageif for two conditions. Normally I'd use the averageifs formula, but, it's not two conditions on one value it's one value where it can be A or B. I've even tried the OR function with no success...

So, what I've tried to do is compensate and do it manually. This is giving me different answers no matter how I do it.

=(SUMIF(L:L,"A",A:A)+SUMIF(L:L,"B",A:A))/(COUNTIF(L:L,"A")+COUNTIF(L:L,"B"))

That should be a manual average, unless I'm mistaken, but the value it's giving me is way off what I'd be expecting. (i.e the above =77.... when the two individually average 90 & 84 respectively.)

So, I'm pretty sure this is wrong. anyone mind pointing out what I'm doing and a neater way of doing this?

Thanks in advance!

-fable

 

[Andrew Poulsom]

Try:

=SUM(SUMIF(A:A,{"A","B"},L:L))/SUM(COUNTIF(A:A,{"A","B"}))

 

[fable]

Still returning a value of 77 unforunately....

But! No worries, i managed to put that into an averageif formula like
=AVERAGEIF(L:L,{"A","B"},A:A) and it's come out to more like what I expected! Many thanks!

I dont suppose anyone knows how to do a forced medianif ?

 

[fable]

hmmmm I seem to have fibbed. That formula above only brings back the average for "A" values, not for A & B....

 

[pgc01]

... and it's come out to more like what I expected!

???

What does this mean? you don't want the exact, correct value? just somethind like what you expected will do?

This is not clear.

IMO, you should do some formula testing, doing the calculations manually for some data values and check that the formula gives you the exact result.

If you want, post an example with 6-8 rows where it does not work as you think it should.

 

[fable]

Hey PGC -- I stated in my first post that i was pretty certain it was giving me an incorrect value when i did a manual (ish) average, as it was highly unlikely that an average of 77 could come from a combination of two groups who averaged 90 & 84 on their own (I would be looking for an average of approx. 88).

Filtering out the table and averaging the relevant fields gives me 88.02

The value it's giving me is way off what I'd be expecting. (i.e the above =77.... when the two individually average 90 & 84 respectively.)

So, I'm pretty sure this is wrong. anyone mind pointing out what I'm doing and a neater way of doing this?

So, trying to find a formula that gives me an exact result (without manually putting each cell individually) is exactly what I'm asking for here. Where the conditions:

Find A & B in column A, and average the relevant values in column L.

 

[pgc01]

Find A & B in column A, and average the relevant values in column L.

In that case, it seems to me that Andrew's formula should solve your problem.

As I said, post some rows of data where you see that the result of the formula is not what you expect so that we can test.

Post
- some rows of data
- Andrew's formula applied to that range
- the result you get
- the result you expected

 

[fable]

let's go one better

https://www.dropbox.com/s/c3rt7moczvpouzn/fable. Example sheet.xlsx

See to the right of the stats box, there are two cells, one with Andrew's formula in place and accurate to the table, and the other as the filtered result which is what i expected to get.

Hope that makes everything more clear!

 

[pgc01]

Sorry, I can't read external files. If you want you can post the data directly, see post #6 here:

http://www.mrexcel.com/forum/excel-questions/677077-xirr-non-contiguous-values.html#6

 

[fable]

						STATS</SPAN>
	Rating	Column1	Gender
	102</SPAN>		c</SPAN>			Rating</SPAN>	All</SPAN>	f</SPAN>	c/g</SPAN>	Andrew's</SPAN>	Filtered</SPAN>
	unrated</SPAN>		c</SPAN>			Average</SPAN>	88.05</SPAN>	87.50</SPAN>		77.506173</SPAN>	88.05042</SPAN>
	95</SPAN>		c</SPAN>			Median</SPAN>	86</SPAN>	88.5</SPAN>
	82</SPAN>		c</SPAN>			over 100</SPAN>	25</SPAN>	10</SPAN>	15</SPAN>
	79</SPAN>		c</SPAN>			from 90-99</SPAN>	24</SPAN>	13</SPAN>	11</SPAN>
	85</SPAN>		c</SPAN>			from 80-89</SPAN>	36</SPAN>	9</SPAN>	27</SPAN>
	92</SPAN>		c</SPAN>			from 70-79</SPAN>	31</SPAN>	13</SPAN>	18</SPAN>
	110</SPAN>		c</SPAN>			under 69</SPAN>	3</SPAN>	3</SPAN>	0</SPAN>
	86</SPAN>		c</SPAN>			unrated</SPAN>	12</SPAN>	4</SPAN>	8</SPAN>
	unrated</SPAN>		c</SPAN>			Total</SPAN>	145</SPAN>	64</SPAN>	81</SPAN>
	78</SPAN>		c</SPAN>
	unrated</SPAN>		c</SPAN>
	100</SPAN>		c</SPAN>
	85</SPAN>		c</SPAN>
	95</SPAN>		c</SPAN>
	unrated</SPAN>		c</SPAN>
	78</SPAN>		c</SPAN>
	74</SPAN>		c</SPAN>
	86</SPAN>		c</SPAN>
	111</SPAN>		c</SPAN>
	103</SPAN>		c</SPAN>
	80</SPAN>		c</SPAN>
	unrated </SPAN>		c</SPAN>
	unrated</SPAN>		c</SPAN>
	85</SPAN>		c</SPAN>
	110</SPAN>		c</SPAN>
	82</SPAN>		c</SPAN>
	104</SPAN>		c</SPAN>
	75</SPAN>		c</SPAN>
	77</SPAN>		c</SPAN>
	104</SPAN>		c</SPAN>
	116</SPAN>		c</SPAN>
	89</SPAN>		c</SPAN>
	84</SPAN>		c</SPAN>
	86</SPAN>		c</SPAN>
	85</SPAN>		c</SPAN>
	82</SPAN>		c</SPAN>
	100</SPAN>		c</SPAN>
	98</SPAN>		c</SPAN>
	87</SPAN>		c</SPAN>
	96</SPAN>		c</SPAN>
	103</SPAN>		c</SPAN>
	117</SPAN>		c</SPAN>
	80</SPAN>		c</SPAN>
	85</SPAN>		c</SPAN>
	unrated</SPAN>		c</SPAN>
	77</SPAN>		c</SPAN>
	76</SPAN>		c</SPAN>
	78</SPAN>		c</SPAN>
	89</SPAN>		c</SPAN>
	74</SPAN>		c</SPAN>
	83</SPAN>		c</SPAN>
	103</SPAN>		c</SPAN>
	92</SPAN>		c</SPAN>
	unrated</SPAN>		c</SPAN>
	83</SPAN>		c</SPAN>
	94</SPAN>		c</SPAN>
	93</SPAN>		g</SPAN>
	unrated</SPAN>		g</SPAN>
	109</SPAN>		g</SPAN>
	83</SPAN>		g</SPAN>
	85</SPAN>		g</SPAN>
	75</SPAN>		g</SPAN>
	86</SPAN>		g</SPAN>
	89</SPAN>		g</SPAN>
	76</SPAN>		g</SPAN>
	101</SPAN>		g</SPAN>
	93</SPAN>		g</SPAN>
	72</SPAN>		g</SPAN>
	75</SPAN>		g</SPAN>
	76</SPAN>		g</SPAN>
	81</SPAN>		g</SPAN>
	87</SPAN>		g</SPAN>
	70</SPAN>		g</SPAN>
	79</SPAN>		g</SPAN>
	88</SPAN>		g</SPAN>
	92</SPAN>		g</SPAN>
			g</SPAN>
	78</SPAN>		g</SPAN>
	94</SPAN>		g</SPAN>
	81</SPAN>		g</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL><COL span=2><COL><COL span=2></COLGROUP>

Is the whole amount of the information. The borders wont come up in any way, but it shows the table relatively well. on the right, you can see my problem. If i filter the table, it comes out to a value closer to what I expected to see, where as Andrew's comes out at a value that's remarkably lower and cannot be right...