Skip to main content
Solved

Problem calculating percent of survey responses when no answer given

  • August 11, 2022
  • 6 replies
  • 35 views
R
Forum|alt.badge.img+7

Hi everyone!

I’m new to learning how to use the deeper aspects of Airtable and how to use linked records.

The problem I’m having … I have a multi-question survey, and am trying to calculate the percentage that people answered a question “yes”. However, some people skipped the question, so I need to remove these from the % calculation.

My goal is to determine what percentage of responses were “yes” from those who actually responded.

I tried by making a lookup table for each question that would only show answers yes/no (so no blank answers), and then the formula COUNTALL ({my lookup field name}) to first tell me how many responded, but I am not getting the correct answer.

Does anyone have recommendations for how to solve this?

Thank you

I

Best answer by Mohamed_Swella1

Thank you for the quick reply @Mohamed_Swellam! I appreciate it…

I had tried the COUNTA as well… the answer was not correct for some reason. I just says “1”… I am attaching screenshots… This is when I was doing it with a formula.

I then tried to use a Rollup on the new table and I was able to get the correct summary (thank you!). But I’m curious if this is what you meant to do? Is there a way to use the rollup without a lookup field? When I tried to do that in my original table, I could not select the field in the same table.

Here’s what I initially tried (incorrect):

Here’s using a rollup, but how can I do in my main table?


Hi Rob,

This make much more sense now :slightly_smiling_face:

Actually since the Yes and No are linked records in the survey table, you can just make a Count field which will count all the linked records.

The formula field is basically counting the number of cells/field, which in your case is one :slightly_smiling_face: In the rollup field its counting the number of records that has a yes / no.

You do not need a lookup field, you need a linked field to use the rollup.

6 replies

Mohamed_Swella1
Forum|alt.badge.img+17

hi @Rob_Jameson ,

Welcome to Airtable Community !

With the little data I have, I think what you are looking for is COUNTA() not COUNTALL()

COUNTA counts the cells that has value, COUNTALL counts all cells even if blank.

Mohamed_Swella1
Forum|alt.badge.img+17

hi @Rob_Jameson ,

Welcome to Airtable Community !

With the little data I have, I think what you are looking for is COUNTA() not COUNTALL()

COUNTA counts the cells that has value, COUNTALL counts all cells even if blank.


HOWEVER, since you are using a Lookup field, this means there are linked records. Why not use a Rollup field instead?

R
Forum|alt.badge.img+7
  • Rob_Jameson
  • Author
  • Known Participant
  • August 11, 2022

HOWEVER, since you are using a Lookup field, this means there are linked records. Why not use a Rollup field instead?


Thank you for the quick reply @Mohamed_Swellam! I appreciate it…

I had tried the COUNTA as well… the answer was not correct for some reason. I just says “1”… I am attaching screenshots… This is when I was doing it with a formula.

I then tried to use a Rollup on the new table and I was able to get the correct summary (thank you!). But I’m curious if this is what you meant to do? Is there a way to use the rollup without a lookup field? When I tried to do that in my original table, I could not select the field in the same table.

Here’s what I initially tried (incorrect):

Here’s using a rollup, but how can I do in my main table?

Mohamed_Swella1
Forum|alt.badge.img+17

Thank you for the quick reply @Mohamed_Swellam! I appreciate it…

I had tried the COUNTA as well… the answer was not correct for some reason. I just says “1”… I am attaching screenshots… This is when I was doing it with a formula.

I then tried to use a Rollup on the new table and I was able to get the correct summary (thank you!). But I’m curious if this is what you meant to do? Is there a way to use the rollup without a lookup field? When I tried to do that in my original table, I could not select the field in the same table.

Here’s what I initially tried (incorrect):

Here’s using a rollup, but how can I do in my main table?


Hi Rob,

This make much more sense now :slightly_smiling_face:

Actually since the Yes and No are linked records in the survey table, you can just make a Count field which will count all the linked records.

The formula field is basically counting the number of cells/field, which in your case is one :slightly_smiling_face: In the rollup field its counting the number of records that has a yes / no.

You do not need a lookup field, you need a linked field to use the rollup.

R
Forum|alt.badge.img+7
  • Rob_Jameson
  • Author
  • Known Participant
  • August 11, 2022

Hi Rob,

This make much more sense now :slightly_smiling_face:

Actually since the Yes and No are linked records in the survey table, you can just make a Count field which will count all the linked records.

The formula field is basically counting the number of cells/field, which in your case is one :slightly_smiling_face: In the rollup field its counting the number of records that has a yes / no.

You do not need a lookup field, you need a linked field to use the rollup.


Fantastic… you have taught me much… Thank you @Mohamed_Swellam!

Mohamed_Swella1
Mohamed_Swella1
Forum|alt.badge.img+17

Fantastic… you have taught me much… Thank you @Mohamed_Swellam!


You’re welcome Rob, my pleasure !

Terms & ConditionsAccessibility statement