DerekK
Board Regular
- Joined
- Jun 18, 2007
- Messages
- 93
- Office Version
- 2003 or older
- Platform
- Windows
Good day,
I am trying to find out what percentage of individuals did not hit a particular condition on the same dates, particularly when one individual is removed from the equation. This sounds so simple but it's driving me mad trying to figure it out. I've tried both PivotTables and SQL but I'm getting nowhere.
SO. I have a bunch of dates, three individuals and two conditions. (I've simplified sensitive data here.) Some dates do not record data for all three individuals (might be three, might be two, might be one, might be none).
I can put this into a PivotTable and figure out what percentage of dates have no individuals who were recorded on that date, hit "Condition2." I've tried dropping "Condition" into the Columns section of a PivotTable, then using a COUNTIF formula. Fine. But I need to find the same figure whenever I put "Individual" in the Filters section and remove one of the individuals.
My data looks something like this:
<html>
<table>
<tr>
<th>Date</th>
<th>Individual</th>
<th>Condition</th>
</tr>
<tr>
<td>Date1</td>
<td>Individual1</td>
<td>Condition1</td>
</tr>
<td>Date1</td>
<td>Individual2</td>
<td>Condition1</td>
<tr>
<td>Date1</td>
<td>Individual3</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date2</td>
<td>Individual1</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date2</td>
<td>Individual2</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date2</td>
<td>Individual3</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date3</td>
<td>Individual2</td>
<td>Condition1</td>
</tr>
<tr>
<td>Date3</td>
<td>Individual3</td>
<td>Condition1</td>
</tr>
</table>
</html>
I hope this makes sense! And I hope you can help!
Thanks,
Derek
I am trying to find out what percentage of individuals did not hit a particular condition on the same dates, particularly when one individual is removed from the equation. This sounds so simple but it's driving me mad trying to figure it out. I've tried both PivotTables and SQL but I'm getting nowhere.
SO. I have a bunch of dates, three individuals and two conditions. (I've simplified sensitive data here.) Some dates do not record data for all three individuals (might be three, might be two, might be one, might be none).
I can put this into a PivotTable and figure out what percentage of dates have no individuals who were recorded on that date, hit "Condition2." I've tried dropping "Condition" into the Columns section of a PivotTable, then using a COUNTIF formula. Fine. But I need to find the same figure whenever I put "Individual" in the Filters section and remove one of the individuals.
My data looks something like this:
<html>
<table>
<tr>
<th>Date</th>
<th>Individual</th>
<th>Condition</th>
</tr>
<tr>
<td>Date1</td>
<td>Individual1</td>
<td>Condition1</td>
</tr>
<td>Date1</td>
<td>Individual2</td>
<td>Condition1</td>
<tr>
<td>Date1</td>
<td>Individual3</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date2</td>
<td>Individual1</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date2</td>
<td>Individual2</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date2</td>
<td>Individual3</td>
<td>Condition2</td>
</tr>
<tr>
<td>Date3</td>
<td>Individual2</td>
<td>Condition1</td>
</tr>
<tr>
<td>Date3</td>
<td>Individual3</td>
<td>Condition1</td>
</tr>
</table>
</html>
I hope this makes sense! And I hope you can help!
Thanks,
Derek
Last edited:
