• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What did I do wrong on averageifs

F

fred

Member
I have a set of data from A1:B4


on column A down: a, b, c, d

on column B down: 2, 51, 3, 7


I tried

AVERAGEIFS($B$1:$B$4,$A$1:$A$4,"a",$A$1:$A$4,"c",$A$1:$A$4,"d")

AVERAGEIFS($B$1:$B$4,$A$1:$A$4,A1,$A$1:$A$4,A3,$A$1:$A$4,A4) but they are all giving me the answer #DIV/0!


I'm using MS07 version. Do I have to activate something to make it work? Where did I do wrong?
 
Fred

You are asking Excel to Average a cell if its value is "a", "c" & "d"

A single cell can only be one of these and hence will be false on the other values


Try something like:

Code: 
=SUMPRODUCT((B2:B5)*(A2:A5={"a","c","d"}))/SUMPRODUCT(1*(A2:A5={"a","c","d"}))
 
hi Hui,


Firs tof all, thanks for the hlep.


So are you saying the "criteria range" cannot come from the same column when I use SUMIFS and AVERAGEIFS? Because that is what I'm seeing on all the samples online and provided by MS help button.


It would be much helpful if we can set the criteria range to be on the same column. As you can see in my first posting here I wanted to find out the average if a certain column contains multiple criterias I want to fulfill.
 
You can use the same column...problem is each criteria is mutually exclusive in your formula. Think of it as asking each cell "use you in the average if your value is a AND your value is c AND your value is d."

No cell can be all 3 at the same time, so nothing gets added into the array, so the AVERAGE of 0 cells gives the DIV/0 error. Now, if your criteria had been something like, "use in average if less than 10 AND greater than 5", this is possible, because the criteria are not mutually exclusive.


To solve your problem, you can use Hui's formula, or you can use this one, if you want to keep the cell references. It's lengthy, but uses the same logic.

=SUMPRODUCT(($B$2:$B$4)*(($A$2:$A$4=A1)+($A$2:$A$4=A3)+($A$2:$A$4=$A$4)))/SUMPRODUCT(1*(($A$2:$A$4=A1)+($A$2:$A$4=A3)+($A$2:$A$4=A4)))
 
Thanks Luke!


I think I got what you mean now. ""use you in the average if your value is a AND your value is c AND your value is d."
 
You must log in or register to reply here.
Back
Top