Automatically Extend Formula in Count function with Additional Row
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 ToolsOptionsEdit. If anyone has any other suggestions I would be pleased to try them. Thanks, D |
Automatically Extend Formula in Count function with Additional Row
=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) "D" wrote in message ... 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 ToolsOptionsEdit. If anyone has any other suggestions I would be pleased to try them. Thanks, D |
Automatically Extend Formula in Count function with Additional Row
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 wrote: 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 ToolsOptionsEdit. If anyone has any other suggestions I would be pleased to try them. Thanks, D -- Dave Peterson |
Automatically Extend Formula in Count function with Additional
Thanks, this worked!
"Dave Peterson" wrote: 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 wrote: 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 ToolsOptionsEdit. If anyone has any other suggestions I would be pleased to try them. Thanks, D -- Dave Peterson |
All times are GMT +1. The time now is 01:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com