Open Forum

I suspect that the subject header has made someone shout "NO" already, but here we go.  I work in the hands-on room in the museum, and we (in the Discovery Center only, not the museum as a whole) currently track our attendance in a very very cumbersome excel system.  We can't upgrade to a different program, so what I'm hoping is that there's someone out there who can recommend a decent template or system that I can use to enter the raw data and can then use it to generate various statistics.  The current method results in daily or weekly or monthly attendance being entered repeatedly for each kind of analysis.  I have a decent working knowledge of the program.  Any suggestions or pointers?

------------------------------
Kathleen McNeece

Smead Discovery Center
Cleveland Museum of Natural History
Cleveland OH
------------------------------

Hello Kathleen,

I completely understand you dilemma. When I first came to the museum, visitation was being tracked/reported manually in Excel using printed reports from CounterPoint as the source. While your situation sounds a little different, I'd be happy to help.

Are you able to email a copy of your current Excel sheet and example of the raw data?

------------------------------
Charles Vann
Community Relations & Visitor Services Manager
Georgia O'Keeffe Museum
Santa Fe NM
------------------------------

In a similar spot over here. In the second year of doing this, I got a lot smarter about making sure that when I entered data, I entered it in a way that was reportable using various functions. 

I find "countif" and "sumif" as some of the most valuable functions for my reporting needs. 

Also, this funky function, which I found after extensive internet searching, which calculates the number of unique values in a range: =SUM(IF(FREQUENCY(MATCH(A1:A30,A1:A30,0),MATCH(A1:A30,A1:A30,0))>0,1)). I use it to calculate how many different days we've completed programs, the number of unique schools served (got to make sure each school is entered the same way, as "PS 43K and PS 43 K" would report as different schools in this scenario.)

It would be helpful to know what kind of reporting you're being asked to do on a weekly (!) or monthly basis - is it just the sum? Or do you need to be more specific, say about age ranges? We do extensive reporting using political districts... 

------------------------------
Alexander Tronolone
Senior Educator
Brooklyn Historical Society
Brooklyn NY
------------------------------

Hello Alexander,

Great pointers! I have never seen the use of the SUM(IF(FREQUENCY(MATCH) formula used in this way.

For Kathleen, it sounds like she is having an efficiency issue with the entry of the raw data into Excel. In this case, I would look at using VBA to develop a "user form" that your volunteer could use to enter the data and then the user form would utilize macros to populate your spreadsheet. This will allow a simple interface for the volunteer--or any other basic user--to populate the spreadsheet with the raw data without potentially mis-entering data into the wrong columns/rows. Just a thought, but requires VBA knowledge to setup. Here is a tutorial: Excel VBA Userform

Another option is to take a look at Google Forms and Google Sheets--combined they can act as a cloud-based database. From my understanding, you can create a custom form that will populate a spreadsheet.

Since we all seem to use Excel, in some form or fashion, I would recommend taking a look at the PowerBI tools now available in Excel. This has afforded me the ability to tap straight into the CounterPoint POS tables and extract the raw data automatically. From this data model, I am able to clean and manipulate the data and connect back into the frontend of Excel for reporting and visualization. While this is a workaround of the canned reports in CounterPoint, it has saved me a significant amount of time in the manual data entry I initially inherited.

------------------------------

Charles Vann
Community Relations & Visitor Services Manager
Georgia O'Keeffe Museum
Santa Fe NM
------------------------------

Alexander - I hadn't even thought about using SumIf, etc - I'll see if I can work that in!  As for what we're looking at, it's purely the number of bodies that come through the Discovery Center each day.  We generate weekly & monthly totals for our monthly report and to compare to previous years.  I'm using Pivot Tables to generate averages, comparisons of specific dates, weekends v weekdays, that sort of thing.  Charles - I would be so grateful if you'd be willing to take a look at it!

Kate

I do something similar with ours. Once I set up the formulas correctly there hasn't been a need to enter the same data repeatedly. 

------------------------------
Kaia Landon
Executive Director
Brigham City Museum-Gallery
Brigham City UT
------------------------------