Ok so let me start off by saying that I know that this is a common problem but none of the common solutions I've tried are working. I will do my best to describe the problem and I am willing to email the spreadsheet to anyone who thinks that they would be able to help.
About the Spreadsheet
'Data Fields' sheet tracks information about individual crisis situations that I review at work. For example:
<tbody>
</tbody>
There are over 200 of these types of entries and in my deep data analysis I am tracking what study flags and age ranges are associated with with risk factors that I choose. I do this analysis on my 'Deep Analysis' Sheet that is set up like this:
<tbody>
</tbody>
The formula I use in C2 is all the way over to HB100 is this:
The cell references change according to the cell. The formula basically produces a 1 or 0 depending on if it finds the risk factor in A2 as well as a risk factor found in A4 on. The formula in column B just adds all the 1s and 0s up for me. This formula actually works for me.
The Problem
Risk factors are divided in to several categories. I would like to calculate associations based on these categories. For example: Suicide is a category with several risk factors in it. The formula above only compares individual risk factors (Suicide - Person previous suicide risk vs Suicide). My solution is this formula that uses a wild card:
Where A2 will contain just the risk category (i.e. Suicide). The issue is that the calculations don't add up correctly compared to when I do it manually.
I really hope all this make sense and I'm completely at a loss around what to try. All the entries in the spreadsheet entered by a drop down menu and all references to the risk factors draw from the same drop down list. I did this to eliminate the possibility of any user errors like extra spaces. I've recalculated the sheet several times and double checked my formulas but I just can't seem to figure this out. Your help would really be appreciated.
Sincerely,
Evon
About the Spreadsheet
'Data Fields' sheet tracks information about individual crisis situations that I review at work. For example:
| Situation # | Risk Factors | Study Flag | Age Ranges |
| DE-2018-1 | Gangs - Gang Association | Homelessness | 12 - 17 |
| Gangs - Threatened by Gang | Risk of Losing Housing | 30 - 39 | |
| Suicide - Current Suicide Risk | Recent Escalation | 0 - 5 |
<tbody>
</tbody>
There are over 200 of these types of entries and in my deep data analysis I am tracking what study flags and age ranges are associated with with risk factors that I choose. I do this analysis on my 'Deep Analysis' Sheet that is set up like this:
| A | B | C | D | |
| 1 | CHOOSE A RISK FACTOR | |||
| 2 | <Risk Factor User Selects> | # | DE-2018-1 | DE-2018-2 |
| 3 | RISK FACTORS | |||
| 4 | Alcohol Use - Alcohol use in the home | 6 | 1 | 0 |
| 5 | Suicide - Person current suicide risk | 7 | 0 | 0 |
| 6 | Suicide - Person previous suicide risk | 9 | 1 | 1 |
| 7 | Parenting - Parent child conflict | 10 | 0 | 1 |
<tbody>
</tbody>
The formula I use in C2 is all the way over to HB100 is this:
Code:
=IF(COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), $A$2)+COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), $A4)=2, 1, 0)The cell references change according to the cell. The formula basically produces a 1 or 0 depending on if it finds the risk factor in A2 as well as a risk factor found in A4 on. The formula in column B just adds all the 1s and 0s up for me. This formula actually works for me.
The Problem
Risk factors are divided in to several categories. I would like to calculate associations based on these categories. For example: Suicide is a category with several risk factors in it. The formula above only compares individual risk factors (Suicide - Person previous suicide risk vs Suicide). My solution is this formula that uses a wild card:
Code:
=IF(COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), [B]"*"&$A$2&"*"[/B])+COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), $A4)=2, 1, 0)Where A2 will contain just the risk category (i.e. Suicide). The issue is that the calculations don't add up correctly compared to when I do it manually.
I really hope all this make sense and I'm completely at a loss around what to try. All the entries in the spreadsheet entered by a drop down menu and all references to the risk factors draw from the same drop down list. I did this to eliminate the possibility of any user errors like extra spaces. I've recalculated the sheet several times and double checked my formulas but I just can't seem to figure this out. Your help would really be appreciated.
Sincerely,
Evon
