if and sumproduct

  • Thread starter Thread starter RichardO
  • Start date Start date
R

RichardO

Hello everyone:

I am really grateful we have such a wonderful excel forum. Thanks fo
all your help.

I have a problem in excel. I would like to find the AVERAGE of th
numbers in column E if the contents of column J is anything other tha
SCI, Code, A/P and Blank i.e."", AND the contents of column M is Yes.
I have the following formula but it's not giving me the correc
answer.


=IF(SUMPRODUCT(('Raw Data'!$J$1:$J999<>"SCI")*('Ra
Data'!$J$1:$J$999<>"Code")*('Raw Data'!$J$1:$J$999<>"A/P")*('Ra
Data'!$J$1:$J$999<>"")*('Raw Data'!$M$1:$M$999="Yes")),AVERAGE('Ra
Data'!$E$1:$E$999))

Thanks a lot.


RichardO
 
Hi
try (the monster)
SUMPRODUCT(('Raw Data'!$J$1:$J999<>"SCI")*('Raw
Data'!$J$1:$J$999<>"Code")*('Raw Data'!$J$1:$J$999<>"A/P")*('Raw
Data'!$J$1:$J$999<>"")*('Raw Data'!$M$1:$M$999="Yes"),'Raw
Data'!$E$1:$E$999)/SUMPRODUCT(('Raw Data'!$J$1:$J999<>"SCI")*('Raw
Data'!$J$1:$J$999<>"Code")*('Raw Data'!$J$1:$J$999<>"A/P")*('Raw
Data'!$J$1:$J$999<>"")*('Raw Data'!$M$1:$M$999="Yes"))
 
You don't need IF and sumproduct in this case. The reason you are
multiplying your criteria together is to use the value 1 when they are all
true, in doing some other calculation. If any of your criteria are false,
one part of your multiplication (product) will be 0, so the value of the
calculation will be 0.

=SUMPRODUCT((E11:E14<>"Bill")*(F11:F14<>"Smith"),G11:G14)/SUMPRODUCT((E11:E1
4<>"Bill")*(F11:F14<>"Smith"))

Something like that would work, tailored to your needs, cumbersome as it is.
The comma in sumproduct seperates arrays to be mutliplied; so the first 2
criteria are evaluated (they will be either 1 if both are true, or 0 if
either is false), then multiplies it by the values in G11:G14. You can then
divide by the number that meet the first 2 criteria, and you'll have an
average.
 
With an = in front obviously

SUMPRODUCT((RngB="Yes")*(RngA<>"SCI")*(RngA<>"A/P")*(RngA<>"Code")*(RngA<>"")*(R
ngVals)) /
SUMPRODUCT((RngB="Yes")*(RngA<>"SCI")*(RngA<>"A/P")*(RngA<>"Code")*(RngA<>""))
 
=AVERAGE(IF((1-ISNUMBER(MATCH('Raw
Data'!$J$1:$J999,{"SCI","Code","A/P"},0)))*('Raw Data'!$J$1:$J999<>"")*('Raw
Data'!$M$1:$M$999="Yes"),'Raw Data'!$E$1:$E$999))

which must be confirmed with control+shift+enter instead of just with enter.
 
Thanks Frank, your formula worked. I'll try out the other formula
suggested. Thanks guys.


RichardO
 
LOL. for some reason frank's message at 8:02 didn't show up here until after
all the other replies even one at 8:39, so I didn't know Frank had even
answered this question!


Dave R. said:
Frank the omnipresent?
Click to expand...
 
Dave R. said:
LOL. for some reason frank's message at 8:02 didn't show up here until after
all the other replies even one at 8:39, so I didn't know Frank had even
answered this question!
Click to expand...

He always do, so always assume he did regardless of what internet tells you
<g>
(Have no idea how he does it though. He produced the same amount of postings
this february that I did in 2003. Whole year, that is.)

Best wishes Harald
 
Hi Harald
quite simple: Currently boring job with nothing to than to watch/check
some batch procedures (lets call this on 4th level support if something
strage happens - and it hasn't happend anything for the last 6
weeks) -> but as we get paid for this - who cares :-)))
 
Gissa Job!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I want one of those kind of
jobs!!!!!!!!!!!!!!!!!!! <g>
 
Ken said:
Gissa Job!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I want one of those kind of
jobs!!!!!!!!!!!!!!!!!!! <g>
Click to expand...

Ken
and the best thing is though we told our customer that this kind of
support is too expensive and not longer required he still insisted that
the project team stays. So we all currently improve some of our skills
while earning money -> best thing i've ever had :-)

Frank
 
Ken Wright said:
Gissa Job!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I want one of those kind of
jobs!!!!!!!!!!!!!!!!!!! <g>
Click to expand...

Second that. Enjoy every minute of it as long as it last.
And I must say you use the time very wise, Frank, it would be very easy to
fall into laziness and apathy in a situation like that.

Best wishes Harald
 
Harald said:
Second that. Enjoy every minute of it as long as it last.
And I must say you use the time very wise, Frank, it would be very
easy to fall into laziness and apathy in a situation like that.

Best wishes Harald
Click to expand...

O.K. so if I need more poeple in this project I know at least two who
will join :-)))))

best wishes to you
Frank
 
Back
Top