sumproduct?

[Rene]

I want to calculate the percentage for when "b" apears in M2:M10 and the
number is between 50 and 100
=SUMPRODUCT(--(B2:L10>50),--(B2:L10<=100)/COUNT(B2:L10))

a1 b2 c2 m2
111 b
99 s
200 s
110 b
55 b
99 s
225 b
b % = 25% (>50 and <=100 b2:l10)

Thank you in advance.

 

[Joel]

50% = 0.5 and 100% = 1.0. when you see 55% in excel it is really sotred as
..55.

 

[Rene]

50 and 100 are numbers, not %s.

I'm using the formulas
SUMPRODUCT(--(B2:B10>50),--(B2:B10<=100)/COUNT(B2:B10)) and
SUMPRODUCT(--(C2:C10>50),--(C2:C10<=100)/COUNT(C2:C10))
to look in seperate columns.

I would like a formula to reduce the numer of columns. Critera being if
m2:m10 is "b" calculate for b2:l2 (formula is in cell n2) and if m2:m10 is
's' calculate for b2:l2 (formula is in cell o2)

Thanks again

 

[Joel]

Try this instead

SUMPRODUCT(--(B2:B10>50),--(B2:B10<=100))/COUNT(B2:B10) and
SUMPRODUCT(--(C2:C10>50),--(C2:C10<=100))/COUNT(C2:C10)

 

[Rene]

I'm confused; not hard. Isn't that the same as I was doing with the two
formulas? I'm trying to elininate column c by entering all the data in
b2:b10 and then using the criteria in m2:m10. If "b" compute the formula in
N2, if criteria is "s" compute the formula in O2.

 

[Joel]

It turns out just adding another set of parethesis to your formula solves the
problem

=SUMPRODUCT(--(B2:B10>50),(--(B2:B10<=100)/COUNT(B2:B10)))

You formula was giving a divide by 0 error. Excel says that empty cells in
arrays are treated as zeroes. I think leaving the parenthesis out was
dimensioning an internal array incorrectly and producing zeroes in the
denominator.

 

[Rene]

Thanks, can I use lookup in (m2:m10) to determine if the formula will be
calculated?

 

[Joel]

I don't completely understand your questions. Don't want to give you an
answer that doesn't apply.

 

[Rene]

I can confuse myself with excel...

Currently I have

a2 b2 c2 d2
105
85
SUMPRODUCT(--(B2:B10>=50),--(B2:b10<=100)/COUNT(B2:b10)) returns 50%

a2 b2 c2 d2
145
75

SUMPRODUCT(--(C2:C10>=50),--(C2:C10<=100)/COUNT(C2:C10)) returns 50%

Would like
a2 b2 c2 d2 m2 n2 o2
105 b 'b' formula returns 50%
145 s 's' formula returns 50%
75 s
85 b

Can I do it?

 

[TomPl]

Do you go by the alias "Novice" or is this a class assignment?

 

[Rene]

yes

 

[Joel]

Put the sumproduct into a new cell like A1. Then multiple the m column
formula by a1.

 

[Rene]

I'm assuming that I can't do it. Thanks again for your help.

Novice/Rene

 

[Rene]

This worke
=SUMPRODUCT((M2:M10)="b")--SUMPRODUCT(--(F2:F10>0),--(F2:F10<=105))/COUNT(F2:F10)


m column does not contain a formula just the criteria 'b' or 's'