# How to calculate the Relative Rate Index based on data collected in tables in Cognito Forms

I'm working on creating a Cognito form and need to know how to do a calculation with two calculated fields. I've pasted a preview below to help explain it. I want to do a calculation in the RRI column comparing the two percentages of total arrests. I'd appreciate help on how to do this. • An image is great as supplemental information, but not everybody can see images. Your question would be much better if it had all of the needed information without the image, so that the image could act as supplemental information.
– ale
Dec 7 '16 at 20:49

Since I am not an expert on RRI calculations, let me state a few assumptions about what you are asking:

1. The `Arrest Rate` for each ethnic group is defined as the number of arrests for that group divided by the total population of that group.

2. The `Relative Rate Index` is the ratio of arrest rates for two different ethnic groups.

3. In this case, let's also assume that the RRI is the ratio of arrest rates of ethnic groups specifically compared with `White/Not Hispanic`.

4. Also, let's assume that we cannot calculate the arrest rate itself since we do not have the total population, only the RRI (tricky problem).

Assuming these assumptions are correct, let's start with the algebra necessary to perform the calculation:

`White Arrest Rate` = `# White Arrests` / (`Total Population` * `White Population %`)

`Ethnic Group Arrest Rate` = `# Ethnic Group Arrests` / (`Total Population` * `Ethnic Group Population %`)

`Relative Rate Index` = `Ethnic Group Arrest Rate` / `White Arrest Rate`

Now, performing a bit of substitution, we have:

`Relative Rate Index` = (`# Ethnic Group Arrests` / (`Total Population` * `Ethnic Group Population %`) ) / ( `# White Arrests` / (`Total Population` * `White Population %`) )

`Total Population` cancels out, and we are left with:

`Relative Rate Index` = (`# Ethnic Group Arrests` / `Ethnic Group Population %`) / (`# White Arrests` / `White Population %` )

Now, switching over to Cognito Forms calculations from algebra, we can calculate both the `Percent of Total Arrests` and the `RRI` solely using information entered into the table.

1. First, select the option to summarize the `Percent of Total City Population` and `Number of Arrests for Calendar Year` columns, which will show the totals and make it easier to write the calculations. This also eliminates the need to capture the total number of arrests, since the table will now calculate this for us. 2. Enter the following calculation for the `Percent of Total Arrests`:

``````=NumberOfArrestsForCalendarYear /
Form.RacialEthnicArrestRates_NumberOfArrestsForCalendarYear_Summary
``````
3. Enter the following calculation for the `RRI`:

``````=(NumberOfArrestsForCalendarYear / PercentOfTotalCityPopulation) /
(Form.RacialEthnicArrestRates.Where(RacialEthnicGroup = "White/Not Hispanic").Sum(NumberOfArrestsForCalendarYear) /
Form.RacialEthnicArrestRates.Where(RacialEthnicGroup = "White/Not Hispanic").Sum(PercentOfTotalCityPopulation) )
``````

When the table is then filled out with information, the results are as follows: Obviously, the RRI for White/Non-Hispanic will always be 1.00 since the numerator and denominator are the same. Please update your post the clarify the question if my assumptions are incorrect!

Finally, here is a shared template showing the form in question, with all the necessary calculations: https://www.cognitoforms.com/templates/shared/ThomasFamily/RelativeRateIndex

• Thank you! I will try that and I think your comment will help me simplify some of my other tables. Dec 7 '16 at 21:29
• Your suggestions helped a lot! However, I'm running into a problem. I created columns to calculate racial and ethic populations from the percentages and added a calculation column for arrest rate (Arrest Number/Ethnic Population * 100,000). I should be able to get the RRI by comparing the arrest rates but I'd like the formula to work for any lable they enter. I tried this formula = ArrestRate2/ArrestRate2.Where(RaceEthnicity = "White/Not Hispanic") but get an error message - No applicable method 'Where' exists in type 'Decimal' at character 25. Can you tell me what is wrong? Dec 8 '16 at 21:28
• Please submit a support request to Cognito Forms, mention my name, and I will take a look at your form and provide specific advice! cognitoforms.com/supportrequest Dec 9 '16 at 14:57