If or Lookup
 

I have a spreadsheet with numbers from 1-20 in column A and a formula that
returns either a yes or 0(blank) in column D. I am trying to set a formula
that would look down A and if it contains a 1 give me the total number of
ones and then the total number of ones with "yes" only then give me the
percentage of 1's with no "yes" Because Yes means you were late and I need to
know the percentage of on time. I need to do this with the 2's in column A
and the 3's in column A and so on until I get to 6's then it will be 6 and
up...
Any help or advice would be greatly appreciated.

=SUMPRODUCT(--(A1:A100=1),--(D1:D100="yes"))/COUNTIF(A1:A100,1)

will give the percentage

--

HTH

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


"LostNFound" wrote in message
...
I have a spreadsheet with numbers from 1-20 in column A and a formula
that
returns either a yes or 0(blank) in column D. I am trying to set a formula
that would look down A and if it contains a 1 give me the total number of
ones and then the total number of ones with "yes" only then give me the
percentage of 1's with no "yes" Because Yes means you were late and I need
to
know the percentage of on time. I need to do this with the 2's in column
A
and the 3's in column A and so on until I get to 6's then it will be 6 and
up...
Any help or advice would be greatly appreciated.

In F1 throught F20 put the #s 1 through 20.
In G1, put the following formula AS AN ARRAY.
=SUM((A1:A8=F1)*(D1:D8="Yes")*1)
what should appear in the cell is...
{=SUM((A1:A8=F1)*(D1:D8="Yes")*1)}
To make an array, while creating or editing the formula, hit
'Ctrl-Shift-Enter'. { and } brackets will surround the formula if you did it
right (NOTE: you can NOT simply put the brackets in.)
Copy this formula from G1 to G20.
This is the count of 'yes' responses to the number in column F
In H1, put...
=COUNTIF(A1:A8,F1)
This is the count of the number in column F
In I1, put...
=G1/H1
This is the percentage of 'yes' for the number in column F
In J1, put...
=1-I1
This is the percentage of 'no' for the number in column F
Copy the formulas in H, I and J down to row 20.

HTH,
Gary Brown





"LostNFound" wrote:

I have a spreadsheet with numbers from 1-20 in column A and a formula that
returns either a yes or 0(blank) in column D. I am trying to set a formula
that would look down A and if it contains a 1 give me the total number of
ones and then the total number of ones with "yes" only then give me the
percentage of 1's with no "yes" Because Yes means you were late and I need to
know the percentage of on time. I need to do this with the 2's in column A
and the 3's in column A and so on until I get to 6's then it will be 6 and
up...
Any help or advice would be greatly appreciated.

Remember to correct for the ranges. In other words, the below example has
data from row 1 to row 8, but your data may be from row 2 to row 2500.
HTH,
Gary Brown


"Gary Brown" wrote:

In F1 throught F20 put the #s 1 through 20.
In G1, put the following formula AS AN ARRAY.
=SUM((A1:A8=F1)*(D1:D8="Yes")*1)
what should appear in the cell is...
{=SUM((A1:A8=F1)*(D1:D8="Yes")*1)}
To make an array, while creating or editing the formula, hit
'Ctrl-Shift-Enter'. { and } brackets will surround the formula if you did it
right (NOTE: you can NOT simply put the brackets in.)
Copy this formula from G1 to G20.
This is the count of 'yes' responses to the number in column F
In H1, put...
=COUNTIF(A1:A8,F1)
This is the count of the number in column F
In I1, put...
=G1/H1
This is the percentage of 'yes' for the number in column F
In J1, put...
=1-I1
This is the percentage of 'no' for the number in column F
Copy the formulas in H, I and J down to row 20.

HTH,
Gary Brown





"LostNFound" wrote:

I have a spreadsheet with numbers from 1-20 in column A and a formula that
returns either a yes or 0(blank) in column D. I am trying to set a formula
that would look down A and if it contains a 1 give me the total number of
ones and then the total number of ones with "yes" only then give me the
percentage of 1's with no "yes" Because Yes means you were late and I need to
know the percentage of on time. I need to do this with the 2's in column A
and the 3's in column A and so on until I get to 6's then it will be 6 and
up...
Any help or advice would be greatly appreciated.

Is this counting the "yes" only with "1" in column A! Is it looking for both
criterias?
I had eight ones in column A and one yes were there was a one and it
returned 12.5% it should have returned 88%

What did I do wrong??

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A100=1),--(D1:D100="yes"))/COUNTIF(A1:A100,1)

will give the percentage

--

HTH

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


"LostNFound" wrote in message
...
I have a spreadsheet with numbers from 1-20 in column A and a formula
that
returns either a yes or 0(blank) in column D. I am trying to set a formula
that would look down A and if it contains a 1 give me the total number of
ones and then the total number of ones with "yes" only then give me the
percentage of 1's with no "yes" Because Yes means you were late and I need
to
know the percentage of on time. I need to do this with the 2's in column
A
and the 3's in column A and so on until I get to 6's then it will be 6 and
up...
Any help or advice would be greatly appreciated.

LostNFound wrote:
Is this counting the "yes" only with "1" in column A! Is it looking for both
criterias?
I had eight ones in column A and one yes were there was a one and it
returned 12.5% it should have returned 88%

What did I do wrong??

"Bob Phillips" wrote:


=SUMPRODUCT(--(A1:A100=1),--(D1:D100="yes"))/COUNTIF(A1:A100,1)

will give the percentage

Replace the "yes" bit with just "".