Total amateur - Can this be done

[Maribeth_Jansse]

I apologize if this forum isn’t for novice advice

I am thinking my ability to clear this hurdle (if it can be cleared) will determine if Airtable is going to be a good choice for me.

I manage a grant process. The software that manages my process gives me reports that can be exported to excel and then csv.

**My goal - ** Create a base where I manage the grant processes. I have started with a table that has all the applicants listed and within that table a link to a table where I have four individual categories of grant applications - and each application is ONE of those categories and linked accordingly

Now when the grants are submitted, I have a team of 8 reviewers who score the applications (three evaluation peices - a score (numeric) a drop down (yes/no) question and a narrative field.

When I export the scores I get a spreadsheet that gives the score by reviewer. There is a row in the spreadsheet for every application with the scores of the eight reviewers. _Mind you, the likelihood of data getting mixed up and the human error factor - using a spreadsheet to analyze the data is torturous _

I don’t know anything about PIVOT tables - or if they even might be a spreadsheet help - but I was most excited about finding this product - hoping I could get a more reliable and analytic result with my data.

My confusion ratchets up when I think about how I accurately import various data and make sure it matches to the right record.

I had added a table where my reviewers are the record …

When I have the scores from the 8 reviewers in spreadsheet form, what (if any) is the best what to make sure the imported data associates the individuals scores for each proposal with the RIGHT proposal?

In my spreadsheet nightmare, I have to carefully cut and paste to make sure the program is lined up correctly with each reviewers score.

How do I get the data from the scoring spreadsheet to recognize (or match) to the right program?

Ultimately I want to create a table that will do the calculations to get the average scores for each program and then create a form where I can give the reviewers the results of the scoring (sorted by high to low) and show them what their score was.

Am I destined to Excel nightmares forever?

[Vincent_Tang]

Some things you could consider

• Of the template bases, which one resembles closest to your problem?

• If your worried about human error factor, consider using “forms” for data entry so users can only add data, not delete or update.

• I’m not entirely sure what your importing issue is. Once you migrate everything to airtable, you can do everything from it.

• For pivot-like functionality, consider learning more about airtables rollup, formula, lookup, and linked fields. They are very powerful for analyzing sets of data. Also, used to do traditinoal excel-like VLOOKUPS as well.

• I wouldn’t worry about pivot tables unless your working in the financial sector and or working with large numeric sets of data. Grant processes don’t benefit from them.

• I have some trouble understanding what your starting data looks like, and what your end result is still. Are you trying to use airtable as a group decision making tool? (e.g. you have a grant, 8 reviewers rate it, the highest rating wins the grant?).

I suggest you scrub some data on your original excel sheet, upload it to airtable, and share it here so its easier to comprehend. And pick a template base that resembles your issue, link that here as well

[Maribeth_Jansse]

Hi Vincent … I’m not sure how to share the table with you.

Hopefully that snap shot helps. When I import the CSV, I have the program name first (as some agencies have more than one application). This snap shot doesn’t show that there are a few more programs below.

When I imported it (as I said above) it created a line item (duplicating for each set) for each person’s score.

The import also may not (in my real world scenario) have every reviewer score each program (some have conflicts of interest and are locked out of scoring.)

I have already imported a table that has the program name (first column), the grant name (corresponding to those on this form) and the amount requested (as well as some other information.)

With the data I have, I want to do TWO things. I want to be able to calculate the average score of each program (adding each reviewer and divide by number of reviewers). With Excel, this means making sure I physically verify how many reviewers score EACH grant.

That result is sorted (high to low) by Grant and is used (print out) to begin the group decision meeting.

The reviewers would like to be able to see a print out of the report above, with additional columns showing their score, yes/no and comments.

(the actual spreadsheet is for over 80 grants - in 3 categories and has scores for the 8 reviewers.

???

[Cathy_Sondergar]

So, my reply has nothing to do with Airtable, but rather Excel. First, did you know that if you simply highlight all the ratings for a given grant, an instantaneous average will be shown in the bottom right corner of your excel window - right below the spreadsheet scroll bar. You can use the filter function first to narrow down to a single program name or grant name. Also, a pivot table might actually be a helpful tool for you if you simply want to present a list of grants with the average score for each.

Sorry to the Airtable people - I do look forward to reading Airtable answers to this question. I am here because I am looking for a non excel solution to some of my projects. Even though I am an excel enthusiast I do recognize its limitations and sometimes irritating complexities.

[Maribeth_Jansse]

When I imported the table (Screenshot below)

As you can see, it created duplicate first row items (key???). Can I/Do I have to go through everyone of these and change it to connect to the table with the program information? By hand? Or is there a way to make airtable look it up.

Same question for the scorers. I created a table with their names, can I easily relate the tables (without having to go into each of reviewers score for the 80 grants and do that manually?

[Maribeth_Jansse]

Thank you Cathy. I have had a many year long trouble grasping pivot tables! I have a friend promising to show me (because I am otherwise pretty good with it.) What I love about Airtable is how it helps me in other way and being able use this process through it would eliminate a need to use admin help (who are awesome but not very excel saavy) who can make a rookie mistake (shifting cells instead of deleted row - etc.!)

[Cathy_Sondergar]

Yes indeed, Pivot Tables are a bit mind bendy. It sounds like you and I are evaluating Airtable for similar reasons. I know a lot about Excel myself but work with committees and volunteers who do not share my comfort level - thus looking for a better platform for all. Fingers crossed that Airtable is the solution!

[Vincent_Tang]

a few questions concerning data:

1. Is Column B directly dependent on column A? I noticed all “summer exihibits” are also all “agencyAsh”, all “Preschool program expansion” are also “That Kid Place”

2. what does “Partial” mean exactly?

That result is sorted (high to low) by Grant and is used (print out) to begin the group decision meeting.

3. Can you manually create what the final print out will look like for 1 days worth of group decision meeting?

4. Which columns / cell data are entered by you, and which ones are entered by the user?

[Maribeth_Jansse]

I believe I nailed it!!! I’m beyond excited!!! Using the lookup function, rollup and function, I have done it!!!

Thank you so much!!!

[Vincent_Tang]

okay glad you figured it out :slightly_smiling_face: