tricky excel formula
 

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

tricky excel formula
 

=SUMIF(A:A,5,B:B)/SUM(B:B)

Regards

Trevor


"Godot" wrote in message
...
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

tricky excel formula
 

One way:

=SUMPRODUCT(--(A2:A100=5),--(B2:B1001))/COUNTIF(A2:A100,5)

In article ,
Godot wrote:

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

tricky excel formula
 

If it is a proportion of all items, use

=AVERAGE(--((A2:A5=5)*(B2:B51)))

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:B51))/COUNTIF(A2:A5,5)

--
HTH

Bob

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

"Godot" wrote in message
...
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