I have a spreadsheet that logs letters I send out to various agencies.
Each time I add a letter to the log, I use a macro which creates a new
line at the top of the log in row 3 so newest letters are at the top.
In column A is the index number of the letter which is simply 1 added
to the previous index no. In column G is a status indicator, either an
"F" or an "N".

I want to count the Fs in column G and divide by the total number of
letters. The total is there in A3. The following formula works fine,
until I add a new row:
=COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3

When I add a new row, the "G3", and both "A3"s increment to G4 and A4
respectively, and the formula omits row 3 from the count. As I add
more lines, the new lines are not included in the count. I'd like to
know how to get around this problem. I've tried several combinations
to make the 3 a constant, one of which is:
=COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3"
but it returns a #REF or sometimes a #VALUE error.
Does anyone know how to write this formula so it works?
Thanks for the help.

Why not just use

=COUNTIF(G:G,"F")/COUNTA(G:G)

--
HTH

Bob

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

"davegb" wrote in message
ups.com...
I have a spreadsheet that logs letters I send out to various agencies.
Each time I add a letter to the log, I use a macro which creates a new
line at the top of the log in row 3 so newest letters are at the top.
In column A is the index number of the letter which is simply 1 added
to the previous index no. In column G is a status indicator, either an
"F" or an "N".

I want to count the Fs in column G and divide by the total number of
letters. The total is there in A3. The following formula works fine,
until I add a new row:
=COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3

When I add a new row, the "G3", and both "A3"s increment to G4 and A4
respectively, and the formula omits row 3 from the count. As I add
more lines, the new lines are not included in the count. I'd like to
know how to get around this problem. I've tried several combinations
to make the 3 a constant, one of which is:
=COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3"
but it returns a #REF or sometimes a #VALUE error.
Does anyone know how to write this formula so it works?
Thanks for the help.

On Oct 4, 4:56 pm, "Bob Phillips" wrote:
Why not just use

=COUNTIF(G:G,"F")/COUNTA(G:G)


It's close, but I'm not getting the correct answer. I think the column
headers in rows 1 & 2 maybe throwing the count off. I tried to add
correction factors for those, but I'm still not getting the correct
number for the percentage. Any other ideas?

--
HTH

Bob

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

"davegb" wrote in message

ups.com...



I have a spreadsheet that logs letters I send out to various agencies.
Each time I add a letter to the log, I use a macro which creates a new
line at the top of the log in row 3 so newest letters are at the top.
In column A is the index number of the letter which is simply 1 added
to the previous index no. In column G is a status indicator, either an
"F" or an "N".

I want to count the Fs in column G and divide by the total number of
letters. The total is there in A3. The following formula works fine,
until I add a new row:
=COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3

When I add a new row, the "G3", and both "A3"s increment to G4 and A4
respectively, and the formula omits row 3 from the count. As I add
more lines, the new lines are not included in the count. I'd like to
know how to get around this problem. I've tried several combinations
to make the 3 a constant, one of which is:
=COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3"
but it returns a #REF or sometimes a #VALUE error.
Does anyone know how to write this formula so it works?
Thanks for the help.- Hide quoted text -

- Show quoted text -

Perhaps

=COUNTIF(G:G,"F")/(COUNTA(G:G)-1)

--
HTH

Bob

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

"davegb" wrote in message
oups.com...
On Oct 4, 4:56 pm, "Bob Phillips" wrote:
Why not just use

=COUNTIF(G:G,"F")/COUNTA(G:G)


It's close, but I'm not getting the correct answer. I think the column
headers in rows 1 & 2 maybe throwing the count off. I tried to add
correction factors for those, but I'm still not getting the correct
number for the percentage. Any other ideas?

--
HTH

Bob

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

"davegb" wrote in message

ups.com...



I have a spreadsheet that logs letters I send out to various agencies.
Each time I add a letter to the log, I use a macro which creates a new
line at the top of the log in row 3 so newest letters are at the top.
In column A is the index number of the letter which is simply 1 added
to the previous index no. In column G is a status indicator, either an
"F" or an "N".

I want to count the Fs in column G and divide by the total number of
letters. The total is there in A3. The following formula works fine,
until I add a new row:
=COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3

When I add a new row, the "G3", and both "A3"s increment to G4 and A4
respectively, and the formula omits row 3 from the count. As I add
more lines, the new lines are not included in the count. I'd like to
know how to get around this problem. I've tried several combinations
to make the 3 a constant, one of which is:
=COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3"
but it returns a #REF or sometimes a #VALUE error.
Does anyone know how to write this formula so it works?
Thanks for the help.- Hide quoted text -

- Show quoted text -

On Oct 5, 4:11 am, "Bob Phillips" wrote:
Perhaps

=COUNTIF(G:G,"F")/(COUNTA(G:G)-1)

--
HTH

Bob

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

"davegb" wrote in message

oups.com...



On Oct 4, 4:56 pm, "Bob Phillips" wrote:
Why not just use

=COUNTIF(G:G,"F")/COUNTA(G:G)

It's close, but I'm not getting the correct answer. I think the column
headers in rows 1 & 2 maybe throwing the count off. I tried to add
correction factors for those, but I'm still not getting the correct
number for the percentage. Any other ideas?

--
HTH

Bob

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

"davegb" wrote in message

roups.com...

I have a spreadsheet that logs letters I send out to various agencies.
Each time I add a letter to the log, I use a macro which creates a new
line at the top of the log in row 3 so newest letters are at the top.
In column A is the index number of the letter which is simply 1 added
to the previous index no. In column G is a status indicator, either an
"F" or an "N".

I want to count the Fs in column G and divide by the total number of
letters. The total is there in A3. The following formula works fine,
until I add a new row:
=COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3

When I add a new row, the "G3", and both "A3"s increment to G4 and A4
respectively, and the formula omits row 3 from the count. As I add
more lines, the new lines are not included in the count. I'd like to
know how to get around this problem. I've tried several combinations
to make the 3 a constant, one of which is:
=COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3"
but it returns a #REF or sometimes a #VALUE error.
Does anyone know how to write this formula so it works?
Thanks for the help.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks, Bob. It turns out that I have to subtract 3, which I can't
figure out why. There are 2 rows of column headers, so I thought I
should subtract 2, but that didn't work. Very confusing. But it works.