tricky excel formula

G

Godot

I have the following columns of data:
Column A Column B
Product Number of faults
1 5
5 1
6 2
5 3
What formula (or set of formulas) will be able to tell me the proportion of
Product number 5's that have more than one fault? (Note - I want a formula
that does not require an autofilter to be applied to my columns of data)

Thanks
 
B

Bob Phillips

If it is a proportion of all items, use

=AVERAGE(--((A2:A5=5)*(B2:B5>1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

If it is a proportion of just teh 5s, use

=SUMPRODUCT(--(A2:A5=5),--(B2:B5>1))/COUNTIF(A2:A5,5)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top