IF/THEN Average Problem

R

raspywench

Hello,

I am looking for a formula to do the following:

If any cells in Column C = B2, Then average the corresponding cells i
Column J...

I was thinkin
=SUMPRODUCT((Data!$C$2:$C$181=B$3)*(AVERAGE(Data!J1:J180)))... But i
just gives me an inaccurate total.

Any help would be appreciated!

Thanks
 
B

Bob Phillips

=AVERAGE(IF(C1:C1000=B2,J1:J1000)

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Biff

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(Data!$C$2:$C$181=B$whatever,Data!J1:J180))

Your explanation states: "If any cells in Column C = B2"

But your formula uses B3. You also have a different range configuration of
Data!J1:J180. As long as both arrays are the EXACT same size, that won't
matter.

Biff
 
R

raspywench

THANKS! :)

So to take it a step further, using the same scenario:

If any cells in Column C = B2, Then average the corresponding cells in
Column J, but only if there is not an Y in Column M

How could that be added into
=AVERAGE(IF(Data!$C$2:$C$181=B$3,Data!J1:J1 80))?

Thank you!
 
B

Bob Phillips

=AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$181="Y"),Data!J1:J1 80))?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Biff

I'm sure Bob meant:

=AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$181<>"Y"),Data!J1:J1 80))

Biff
 
B

Bob Phillips

Don't think so, OP said ... not an Y in Column M

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Biff

Don't think so, OP said ... not an Y in Column M

Data!$M$2:$M$181<>"Y"

What am I not understanding here?

Biff
 
R

raspywench

Thanks Biff and Bob!

But both of them seem to calculate the inaccurately or give me a
error.

Any other ideas about this?

Thanks
 
B

Biff

Hi!

Hmmm.........

I see that you're posting from ExcelForum

ExcelForum allows attachments. Can you post your file THERE so I can take a
look?

Biff
 
B

Bob Phillips

Your formula was

=AVERAGE(IF(Data!$C$2:$C$18=B$3,Data!$N$2:$N$18<>"X"),Data!$J$2:$J$18))

it should be

=AVERAGE(IF((Data!$C$2:$C$18=B$3)*(Data!$N$2:$N$18<>"X"),Data!$J$2:$J$18))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
P

Peo Sjoblom

Of if he wants to use it that way he needs to add another IF

=AVERAGE(IF(Data!$C$2:$C$18=B$3,IF(Data!$N$2:$N$18<>"X",Data!$J$2:$J$18)))


--

Regards,

Peo Sjoblom

Bob Phillips said:
Your formula was

=AVERAGE(IF(Data!$C$2:$C$18=B$3,Data!$N$2:$N$18<>"X"),Data!$J$2:$J$18))

it should be

=AVERAGE(IF((Data!$C$2:$C$18=B$3)*(Data!$N$2:$N$18<>"X"),Data!$J$2:$J$18))

--

HTH

RP
(remove nothere from the email address if mailing direct)


in message news:[email protected]...
Click to expand...
 
B

Biff

Hi!

OK, just some missing parenths.......

=AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$N$2:$N$181<>"X"),Data!$J$2:$J$181))

Returns 8.14

Biff
 
B

Biff

On a side note:

You're using array formulas:

{=SUM(IF(Data!$C$2:$C$181=B$3,Data!$K$2:$K$181))}

It's not necessary to use arrays for that. Try this instead:

=SUMIF(Data!$C$2:$C$181,B$3,Data!$K$2:$K$181)

Biff
 
B

Bob Phillips

He doesn't want to, he mis-transcribed what he was given.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top