Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extend graph axis automatically in excel | Charts and Charting in Excel | |||
How to Extend Range Automatically | Excel Worksheet Functions | |||
cell ref in function formula change when inserting additional colu | Excel Worksheet Functions | |||
how to extend data to next sheet automatically during sql query | Excel Discussion (Misc queries) | |||
Excel should not automatically extend series or fill values | Excel Discussion (Misc queries) |