Help on a formula

[ForSale]

Thanks Ron,
I put in your formula and it works with one exception. This is kind o
hard to explain. there are three rows for every name in the list (7
names, 225 rows). like i said earlier every third row is an average o
the above two. Not everyone will have a score, some will have one, som
will have two. The problem with your previous formula is that if ther
are 10 names and 5 have 2 scores each and the other 5 have 0, it show
100% when it should be 50%. no one will have a score in the second ro
without a score in the first row. my suggestion would be to have i
only look at the first of the three rows for each person; but of cours
i'm not skilled with formulas enough to change the one that yo
provided.
Please help me change the previous formula to these specifications.
thanks

 

[Ron Rosenfeld]

Thanks Ron,
I put in your formula and it works with one exception. This is kind of
hard to explain. there are three rows for every name in the list (75
names, 225 rows). like i said earlier every third row is an average of
the above two. Not everyone will have a score, some will have one, some
will have two. The problem with your previous formula is that if there
are 10 names and 5 have 2 scores each and the other 5 have 0, it shows
100% when it should be 50%. no one will have a score in the second row
without a score in the first row. my suggestion would be to have it
only look at the first of the three rows for each person; but of course
i'm not skilled with formulas enough to change the one that you
provided.
Please help me change the previous formula to these specifications.
thanks.

It sounds as if now what you want is the number of "first row entries" in
column C divided by the number of unique names in column A.

Since every name in column A is repeated three times, then the number of unique
names would be =COUNTA(A4:A224)/3.

To check all the "first row entries" for the presence of a number entry, you
need to produce an array consisting of every third cell starting with your
first row cell which, if I understand you correctly, is C4.

That array of addresses can be produced by the formula:

=ADDRESS(1+3*ROW(INDIRECT("1:74")),3)

The ROW(INDIRECT... function produces an array of numbers from 1 to 74.
Multiplying that by 3 and adding 1 then produces an array of numbers equal to
{4,7,10...}. We then use that array as the _row_ argument in the ADDRESS
function. The 3 is the _column_ argument for the ADDRESS function which is
Column C. So we now have an array of addresses -- C4, C7, etc.

To make that argument adjust for different numbers of names, we change the "74"
as needed. Alternatively, we could change it automagically by changing the
formula a bit:

=ADDRESS(1+3*ROW(INDIRECT("1:"&COUNTA(A4:A5000)/3)),3)

The 5000 can be any number large enough to encompass the potential range of
names.

The ADDRESS function returns a string, and not the values in the address. To
obtain that we use INDIRECT. So to obtain an array of the values in those
first rows, we use:

=INDIRECT(ADDRESS(1+3*ROW(INDIRECT("1:"&COUNTA(A4:A5000)/3)),3))

Finally, we want to count the number of those rows which have an entry. For
reasons having to do with how Excel implements this kind of array function, we
need to TRANSPOSE the result, and then we can count it.

So the formula to COUNT the number of "first row entries" would be the
*array-entered* formula:

=COUNT(TRANSPOSE(INDIRECT(ADDRESS(1+3*ROW(INDIRECT("1:"&COUNTA(A4:A5000)/3)),3))))

To *array-enter* a formula, after typing or pasting it into the formula bar,
instead of just hitting <enter>, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

Finally, combine the two to obtain the percent of NAMES that have entries in
the first row:

=COUNT(TRANSPOSE(INDIRECT(ADDRESS(1+3*ROW(INDIRECT(
"1:"&COUNTA(A4:A5000)/3)),3))))/(COUNTA(A4:A5000)/3)

Again, this formula must be *array-entered*.

Hopefully my explanation is clear enough that you can successfully modify the
formula yourself, if need be.






--ron