Automatically Extend Formula in Count function with Additional Row

[D]

I am creating a form in excel about complaint resolution. It is a protected
sheet and only some cells are unlocked to edit. Users will also be able to
add/delete rows. One column asks the user whether or not the complaint was
resolved. A drop down list allows them to choose yes or no. The last row of
the sheet will calculate what percentage of complaints were resolved. An
example is below:

A
1 Yes
2 No
3 Yes
4 Yes
5 No
6 =countif(A1:A5,"Yes")/counta(A1:A5)

If a 6th complaint comes in, I want the user to be able to add another row
and have the total formula (now in A7) extend to read
=countif(A1:A6,"Yes")/counta(A1:A6). The user will not be able to update this
manually as the total column will be locked.

I have read the help topic "Extend formats and fomulas to additional rows"
and I have checked to ensure that the 'Extend data range format and formulas'
box is selected under Tools>Options>Edit.

If anyone has any other suggestions I would be pleased to try them.

Thanks,
D

 

[Bob Phillips]

=COUNTIF(OFFSET(A1,,,ROW()-1,1),"Yes")/COUNTA(OFFSET(A1,,,ROW()-1,1))

--
HTH

Bob

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

 

[Dave Peterson]

I'd put this total in row 1 (and headers in row 2???), then I could make that
range as big as I wanted:

=countif(a3:a65536,"yes")/counta(a3:a65536)

But you could also use a formula like this in A6:
=COUNTIF(A1:OFFSET(A6,-1,0),"Yes")/COUNTA(A1:OFFSET(A6,-1,0))

 

[D]

Thanks, this worked!

I'd put this total in row 1 (and headers in row 2???), then I could make that
range as big as I wanted:

=countif(a3:a65536,"yes")/counta(a3:a65536)

But you could also use a formula like this in A6:
=COUNTIF(A1:OFFSET(A6,-1,0),"Yes")/COUNTA(A1:OFFSET(A6,-1,0))