Hi Excel Gods
I have 2 formulae that work just fine
=AVERAGEIF($D$5:$D$340,">=150000")
=AVERAGEIF($D$5:$D$340,"<50000")
How do I get one to show the average of the values of the instances in between, ie <150000 AND >=50000?
=AVERAGEIF($D$5:$D$340,"<150000",$D$5:$D$340)*AND(AVERAGEIF($D$5:$D$340,">=50000",$D$5:$D$340)) returns an incorrect value
as does =SUMIF(D$5:D$340,"<150000")-(SUMIF(D$5:D$340,">=50000")))/(COUNTIF(D$5:D$340,"<150000")-COUNTIF(D$5:D$340,">=50000"))
What am I missing?
See the AVERAGEIFS function.
Entia non sunt multiplicanda sine necessitate
I think I see your errors....
=averageif(1) * averageif(2) / 2
//You're not taking the averages of the averages because you're not dividing their product by 2!
//What is that AND() doing? It's going to force the value to 1 or 0?
=( SUMIF(1) + SUMIF(2) ) / ( COUNTIF(1) + COUNTIF(2) )
//As it stands the syntax is 1 + 2/(3+4) not (1+2) / (3+4)
However, like shg pointed out, you can use AVERAGEIFS() to do all of that with one built-in function instead.
Last edited by ben_hensel; 11-13-2013 at 12:46 PM.
I should have said "also" or "as well as" rather than "AND" which was only going to confuse matters when discussing Excel.
I still can't get the SUMIF - COUNTIF way to work but AVERAGEIFS has solved it
thanks both
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks