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).
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Encounter#[/TD]
[TD]NB[/TD]
[TD]LOS[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]78901[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]89012[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]90123[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]01234[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
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).
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Encounter#[/TD]
[TD]NB[/TD]
[TD]LOS[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]78901[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]89012[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]90123[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]01234[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
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.
