Ok, I'm new in my position and my formal education in excel is far more limited than the informal education that I've been giving myself in a hurry in order to keep up with my responsibilities. So I know only what I've done and absolutely nothing else, if that makes any sense. One thing that keeps confounding me is how many obvious functions excel doesn't seem to have and how badly it copes once you have formulaic-ally created those functions for it.
I will describe first my actual need and then what I have done and I hope someone won't mind pointing me in the right direction.
Needs
A spreadsheet in which I can input data gathered from web form responses, which can analyse the data based on conditional factors. In my case, I need to make analysis based on geographical region and what calendar quarter the report occurred in. Both fields are handled by the web form so that data is inherent and standardised in all records harvested. The questions contained on the form are either Multiple choice with 1 answer, or multiple choice with multiple answers. The statistics I need to generate are fairly simple. E.q Percentage of cases occurring in region 1, quarter 2 which answered "Yes" to question 3.
Now all of this is very simple to do on a case by case basis. But I need to produce these statistics for 20 regions, once every quarter, with new records pouring in consistently over the course of the year. So manually filtering and processing the data would be very time consuming.
So far I have used some very tricky formulas to do some very simple things. So I have One sheet which contains all of the records in a table. The second sheet conditionally populates based on criteria selected in the third sheet, which analyses the data in the second sheet and produces statistics for each question asked. While this just about works at the moment, it is painfully slow, requiring minutes of calculating time any time a single record is amended or added and twice as long as that any time I change the criteria on sheet 3. Further more, the extreme complexity of some of the formula used to conditionally populate the table have left me unable to allow for filtering on a single criteria. So for example, all records from quarter 1 is impossible, as without both criteria selected the formulas don't work correctly. But the biggest problem I'm having is that excel seems incapable of accepting additional records.
I started off with about a hundred webforms as a test case and I have that up and running and I assumed that I would be able to simply paste in new records and have them added to the tables. But excel has a kiniption fit any time I try to do this and refuses to treat the new entries as being part of the table which is referenced in sheet 2, making all new records invisible to my conditionally populated look up sheet 2. If I can get this system running then I will be expecting between 2000 and 4000 records a year to be submitted, so problems with adding in records is a huge issue for me.
Below I'll show some formula and what they're doing to give people an idea of what level I'm working at.
Helper Column
=SUM(OR('CountyQuart Stats'!$D$3=0,(AND([@County]='CountyQuart Stats'!$A$3,[@Quarter]='CountyQuart Stats'!$B$3))),AJ1)
This checks the current records details against the criteria and assigns a unique number if both instances are true.
Conditionally filtered table (Spreadsheet 2)
=IF($A2>'CountyQuart Stats'!$C$3,"",INDEX(Table13[Time spent on case],MATCH($A2,Table13[Helper],0)))
This pulls all uniquely numbered records according to the helper column and shows them in the filtered list. The inability to use an absolute reference to a table column is infuriating as this has to be used for each column.
Answer counting
=SUM(LEN('Filtered List'!F:F)-LEN(SUBSTITUTE('Filtered List'!F:F,"THL","")))/3
Used to count instances of answers to questions.
I understand that in many ways the functions I am looking for are more akin to an Access database. However, the whole purpose of this is to be able to analyse the data and produce graphic representations with charts and graphs. If anyone can offer any advice I would be most grateful.
I will describe first my actual need and then what I have done and I hope someone won't mind pointing me in the right direction.
Needs
A spreadsheet in which I can input data gathered from web form responses, which can analyse the data based on conditional factors. In my case, I need to make analysis based on geographical region and what calendar quarter the report occurred in. Both fields are handled by the web form so that data is inherent and standardised in all records harvested. The questions contained on the form are either Multiple choice with 1 answer, or multiple choice with multiple answers. The statistics I need to generate are fairly simple. E.q Percentage of cases occurring in region 1, quarter 2 which answered "Yes" to question 3.
Now all of this is very simple to do on a case by case basis. But I need to produce these statistics for 20 regions, once every quarter, with new records pouring in consistently over the course of the year. So manually filtering and processing the data would be very time consuming.
So far I have used some very tricky formulas to do some very simple things. So I have One sheet which contains all of the records in a table. The second sheet conditionally populates based on criteria selected in the third sheet, which analyses the data in the second sheet and produces statistics for each question asked. While this just about works at the moment, it is painfully slow, requiring minutes of calculating time any time a single record is amended or added and twice as long as that any time I change the criteria on sheet 3. Further more, the extreme complexity of some of the formula used to conditionally populate the table have left me unable to allow for filtering on a single criteria. So for example, all records from quarter 1 is impossible, as without both criteria selected the formulas don't work correctly. But the biggest problem I'm having is that excel seems incapable of accepting additional records.
I started off with about a hundred webforms as a test case and I have that up and running and I assumed that I would be able to simply paste in new records and have them added to the tables. But excel has a kiniption fit any time I try to do this and refuses to treat the new entries as being part of the table which is referenced in sheet 2, making all new records invisible to my conditionally populated look up sheet 2. If I can get this system running then I will be expecting between 2000 and 4000 records a year to be submitted, so problems with adding in records is a huge issue for me.
Below I'll show some formula and what they're doing to give people an idea of what level I'm working at.
Helper Column
=SUM(OR('CountyQuart Stats'!$D$3=0,(AND([@County]='CountyQuart Stats'!$A$3,[@Quarter]='CountyQuart Stats'!$B$3))),AJ1)
This checks the current records details against the criteria and assigns a unique number if both instances are true.
Conditionally filtered table (Spreadsheet 2)
=IF($A2>'CountyQuart Stats'!$C$3,"",INDEX(Table13[Time spent on case],MATCH($A2,Table13[Helper],0)))
This pulls all uniquely numbered records according to the helper column and shows them in the filtered list. The inability to use an absolute reference to a table column is infuriating as this has to be used for each column.
Answer counting
=SUM(LEN('Filtered List'!F:F)-LEN(SUBSTITUTE('Filtered List'!F:F,"THL","")))/3
Used to count instances of answers to questions.
I understand that in many ways the functions I am looking for are more akin to an Access database. However, the whole purpose of this is to be able to analyse the data and produce graphic representations with charts and graphs. If anyone can offer any advice I would be most grateful.
