averaging based on multiple criteria

  • Thread starter Thread starter Julz
  • Start date Start date
J

Julz

I'm having problems with the following:

If column D="n" average column k

also

If column d="n" give me percentage of column k <16


thanx in advance,
~julz
 
Hi
for the first try the array formula (entered with CTRL+SHFT+ENTER):
=AVERAGE(IF(D1:D100="n",K1:K100))

For the second question try (non arra formula)
=SUMPRODUCT(--(D1:D100="n"),--(K1:K100<16)/COUNTIF(D1:D100,"n")
and format as percentage
 
Hi
sorry the second one had a missing bracket. Should read:
=SUMPRODUCT(--(D1:D100="n"),--(K1:K100<16))/COUNTIF(D1:D100,"n")

But they shouldn't return a #REF error
What are the exact formulas you have used
 
Hi Julz

The first one is working
Note :(entered with CTRL+SHFT+ENTER)

The second is missing one ) at the end of the formula
 
#1 as an array
{=AVERAGE(IF(D2:D19000="n",K2:K19000))}

#2
=SUMPRODUCT(--(D2:D19999="n"),--(K2:K19999<16))/COUNTIF(D2:D19999,"n")


row 1 contains column headers which is why I'm using D2, K2, etc.

also, this spreadsheet is populated with new data weekly where there is
a variance between how many rows will contain data, which is why I'm
using 19999.

Hope this helps.
 
Let me apologize. I really should have coffee if I'm going to start
working @ 5am on a Sunday. :)

I found my problem with #REF.
I left out a critical part. The data is on a separate sheet called "open".
{=AVERAGE(IF(open!D2:D19000="n",open!K2:K19000))}

After correcting, I'm no longer getting #REF, but #2 is returning 0%.
 
Hi
what is the exact second fomrula?
it should read:
=SUMPRODUCT(--(open!D2:D19000="n"),--(open!K2:K19000<16))/COUNTIF(open!
D2:D19000,"n")
 
Back
Top