AverageIF problem... (Continued)

  • Thread starter Thread starter Jay Gustafson
  • Start date Start date
J

Jay Gustafson

Hello,

Chip & Frank, thank you for helping me out with my origional problem. But unfortunatly, I went about it wrong. Your suggestions did work, but in the way that I wanted, which that's my mistake.

I guess what I do want, is the values summed below, but then averaged at the end.

=SUMIF($H$3:$H$23,C11,$K$3:$K$23)+SUMIF($H$3:$H$23,C12,$K$3:$K$
23)+SUMIF($H$3:$H$23,C13,$K$3:$K$23)+SUMIF($H$3:$H$23,C14,$K$3:
$K$23)+SUMIF($H$3:$H$23,C15,$K$3:$K$23)+SUMIF($H$3:$H$23,C16,$K
$3:$K$23) then, average all the sums.
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=SUMPRODUCT(($H$3:$H$23=TRANSPOSE(C11:C16))*($K$3:$K$23))/SUMPRODUCT(--
($H$3:$H$23=TRANSPOSE(C11:C16)))

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag Hello,

Chip & Frank, thank you for helping me out with my origional problem.
But unfortunatly, I went about it wrong. Your suggestions did work,
but in the way that I wanted, which that's my mistake.

I guess what I do want, is the values summed below, but then averaged
at the end.

=SUMIF($H$3:$H$23,C11,$K$3:$K$23)+SUMIF($H$3:$H$23,C12,$K$3:$K$
23)+SUMIF($H$3:$H$23,C13,$K$3:$K$23)+SUMIF($H$3:$H$23,C14,$K$3:
$K$23)+SUMIF($H$3:$H$23,C15,$K$3:$K$23)+SUMIF($H$3:$H$23,C16,$K
$3:$K$23) then, average all the sums.
 
=SUMPRODUCT(--(ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$16)/SUMPROD
UCT(--(ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)))

Needs just enter.

Hello,

Chip & Frank, thank you for helping me out with my origional problem. But
unfortunatly, I went about it wrong. Your suggestions did work, but in the
way that I wanted, which that's my mistake.

I guess what I do want, is the values summed below, but then averaged at the
end.

=SUMIF($H$3:$H$23,C11,$K$3:$K$23)+SUMIF($H$3:$H$23,C12,$K$3:$K$
23)+SUMIF($H$3:$H$23,C13,$K$3:$K$23)+SUMIF($H$3:$H$23,C14,$K$3:
$K$23)+SUMIF($H$3:$H$23,C15,$K$3:$K$23)+SUMIF($H$3:$H$23,C16,$K
$3:$K$23) then, average all the sums.
 
First, Please stay in the ORIGINAL thread. Try this idea. Tested with A in c11 and B in c12

Sub AverageIFmulti()
For Each c In Range("c11:c12") 'Selection
ms = ms & "," & """" & c & """"
Next
ms = "{" & Right(ms, Len(ms) - 1) & "}"
MsgBox ms
Range("c1").Formula = _
"=sumproduct((h3:h23=" & ms & ")*k3:k23)/sumproduct((h3:h23=" & ms & ")*1)"
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hello,

Chip & Frank, thank you for helping me out with my origional problem. But unfortunatly, I went about it wrong. Your suggestions did work, but in the way that I wanted, which that's my mistake.

I guess what I do want, is the values summed below, but then averaged at the end.

=SUMIF($H$3:$H$23,C11,$K$3:$K$23)+SUMIF($H$3:$H$23,C12,$K$3:$K$
23)+SUMIF($H$3:$H$23,C13,$K$3:$K$23)+SUMIF($H$3:$H$23,C14,$K$3:
$K$23)+SUMIF($H$3:$H$23,C15,$K$3:$K$23)+SUMIF($H$3:$H$23,C16,$K
$3:$K$23) then, average all the sums.
 
One more option:

=AVERAGE(IF(H3:H23=TRANSPOSE(C11:C16),K3:K23))

array entered.

Please stay in the original thread.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.


"Jay Gustafson" <[email protected]> skrev i en meddelelse
Hello,

Chip & Frank, thank you for helping me out with my origional problem. But
unfortunatly, I went about it wrong. Your suggestions did work, but in the
way that I wanted, which that's my mistake.

I guess what I do want, is the values summed below, but then averaged at the
end.

=SUMIF($H$3:$H$23,C11,$K$3:$K$23)+SUMIF($H$3:$H$23,C12,$K$3:$K$
23)+SUMIF($H$3:$H$23,C13,$K$3:$K$23)+SUMIF($H$3:$H$23,C14,$K$3:
$K$23)+SUMIF($H$3:$H$23,C15,$K$3:$K$23)+SUMIF($H$3:$H$23,C16,$K
$3:$K$23) then, average all the sums.
 
Aladin Akyurek said:
=SUMPRODUCT(--(ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$16)/SUMPROD
UCT(--(ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)))

Needs just enter.
Click to expand...

Also needs adjusting. Must have been late :-)

Two right-parentheses are missing, and $K$3:$K$16
must be $K$3:$K$23.
 
Leo Heuser said:
=SUMPRODUCT(--(ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$16)/SUMPROD

Also needs adjusting. Must have been late :-)

Two right-parentheses are missing, and $K$3:$K$16
must be $K$3:$K$23.
Click to expand...

Thanks. Just for the record, also because it contains a faster
conditional...

=SUMPRODUCT(--ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$16)/SUMPRODU
CT(--ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)))
 
Darn it... Forgot to correct for range:

=SUMPRODUCT(--ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$23)/SUMPRODU
CT(--ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)))
 
Aladin Akyurek said:
Darn it... Forgot to correct for range:

=SUMPRODUCT(--ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$23)
/SUMPRODUCT(--ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)))
Click to expand...
....

If the goal were efficiency, why not one array formula AVERAGE(IF(...))?

=AVERAGE(IF(COUNTIF($C$11:$C$16,$H$3:$H$23),$K$3:$K$23))

The MATCH calls may individually return more quickly than each COUNTIF call,
but going through the col C and H ranges twice would offset that. Leo's
array formula may be faster, but it generates two 6 by 21 cell temporary
arrays, so it'd use more memory, and generating the redundant columns must
take some extra time.
 
Harlan Grove said:
...

If the goal were efficiency, why not one array formula AVERAGE(IF(...))?

=AVERAGE(IF(COUNTIF($C$11:$C$16,$H$3:$H$23),$K$3:$K$23))

The MATCH calls may individually return more quickly than each COUNTIF call,
but going through the col C and H ranges twice would offset that. Leo's
array formula may be faster, but it generates two 6 by 21 cell temporary
arrays, so it'd use more memory, and generating the redundant columns must
take some extra time.
Click to expand...

The remark pertains to the ISNUMBER(MATCH(...)) bit rather than the whole
formula in a context where TRANSPOSE() was forwarded. A
control+shift+entered formula is of course more appropriate. I think I'd opt
for one with the ISNUMBER(MATCH(...)) idiom:

=AVERAGE(IF(ISNUMBER(MATCH($H$3:$H$23,$C$11:$C$16,0)),$K$3:$K$23))

In an another we had the same situation COUNTIF() vs ISNUMBER(MATCH(...)). I
didn't find time to come up with timing data, but judging from:

http://www.mrexcel.com/board2/viewtopic.php?t=40233

I expect the formula with ISNUMBER(MATCH(...)), on the average, to perform
faster

..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AVERAGEIF problem 6
Nesting SUMIF within SUMPRODUCT 10
Sumif with an array for criteria 3
Sum/Count of cells spanning different columns/rows 3
Entering only 2 values out of 3 - throws up message as false 2
Sumif to return a blank if sum range is blank 3
Nested IF Function problem for Excel 2003 7
Sports Tipping Formula! 1

Back
Top