For my work my productivity is tracked and recorded, however the database software our company uses is garbage and I have an excel spreadsheet that I prefer to use.
So what I track is:
Depending on the LOS and if it is a NB or not, will depict how many minutes you are allotted for that encounter. For instance a 1-3 day LOS you are given 32.5 min, whereas a 1-3 day LOS NB you are given 22.5 min.
What I want to be able to do is have excel count up how many 1-3 day LOS encounters I have for the week, how many 1-3 day NB LOS encounters, 4-6 day LOS, etc.
I was thinking that this would involve an IF statement to determine if column 2 has a "Y" making it a NB encounter, and then the COUNTIF statement to count how many encounters are 1-3 day LOS (Column 3).
<tbody>
</tbody>
For the above table, there are:
5x 1-3 day LOS NB encounters, (1, 2, 2, 1, 1)
3x 1-3 day LOS encounters (2, 3, 3)
1x 4-6 day LOS encounters (4)
How would you write a formula that would be able to Count each of the above values? I realize I would need a separate formula for each value range, 1-3 day LOS, 1-3 day LOS NB, 4-6 day LOS. Would I have to have a separate formula for 1 day LOS, 2 day LOS, 3 day LOS, then SUM those values? I need to be able to keep the NB encounters separate from the normal ones as they are given a different amount of time, hence my thought for the IF statement. Please let me know what you think.
So what I track is:
- Encounter #
- If it is a NB (NewBorn), Y for yes, nothing if not
- The LOS (Length Of Stay), #
Depending on the LOS and if it is a NB or not, will depict how many minutes you are allotted for that encounter. For instance a 1-3 day LOS you are given 32.5 min, whereas a 1-3 day LOS NB you are given 22.5 min.
What I want to be able to do is have excel count up how many 1-3 day LOS encounters I have for the week, how many 1-3 day NB LOS encounters, 4-6 day LOS, etc.
I was thinking that this would involve an IF statement to determine if column 2 has a "Y" making it a NB encounter, and then the COUNTIF statement to count how many encounters are 1-3 day LOS (Column 3).
| Encounter# | NB | LOS |
| 23456 | 2 | |
| 34567 | Y | 1 |
| 45678 | Y | 2 |
| 56789 | 3 | |
| 67890 | Y | 2 |
| 78901 | Y | 1 |
| 89012 | Y | 1 |
| 90123 | 4 | |
| 01234 | 3 |
<tbody>
</tbody>
For the above table, there are:
5x 1-3 day LOS NB encounters, (1, 2, 2, 1, 1)
3x 1-3 day LOS encounters (2, 3, 3)
1x 4-6 day LOS encounters (4)
How would you write a formula that would be able to Count each of the above values? I realize I would need a separate formula for each value range, 1-3 day LOS, 1-3 day LOS NB, 4-6 day LOS. Would I have to have a separate formula for 1 day LOS, 2 day LOS, 3 day LOS, then SUM those values? I need to be able to keep the NB encounters separate from the normal ones as they are given a different amount of time, hence my thought for the IF statement. Please let me know what you think.
