Can this be done in Excel?

SNAPCymru

New Member
Joined
Sep 1, 2014
Messages
4
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
From what you describe, I suspect a pivot table would solve most of your problems.

I'm not really clear on why you're using the formulas you are, so I won't comment on that, other than to say that you can fix table column references by referring to them as table[column]:table[column] rather than just table[column]. Also, copy/pasting formulas with table references behaves differently to filling them.
 
Upvote 0
From what you describe, I suspect a pivot table would solve most of your problems.

I'm not really clear on why you're using the formulas you are, so I won't comment on that, other than to say that you can fix table column references by referring to them as table[column]:table[column] rather than just table[column]. Also, copy/pasting formulas with table references behaves differently to filling them.


Have tried using pivot tables but they are very unhelpful for the type of data I am analysing. Either that or I'm using them wrong.
Because most of the data is textual and not numerical, it is very difficult to persuade the pivot table to do anything that doesn't look completely ridiculous.
I was able to use a pivot table once I had converted all of the "Yes/No" answer questions into 1's and 0's. But this accounts for a very small part of the data analysis that I am using.

I am thinking that I might scrap the table formatting altogether and just use whole column references, although I am not sure if the index function still works with this.
 
Upvote 0

Forum statistics

Threads
1,222,096
Messages
6,163,912
Members
451,865
Latest member
dunworthc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top