BACKGROUND:
I'm trying to create a spreadsheet that would help me do the monthly
schedule for our nurses more efficiently. Right now, the spreadsheet is just
fully manual, add all the dates (to six sheets), review the schedules cell by
cell to make sure we have enough people scheduled and then take the daily
schedules and apply to yet another spreadsheet that shows the daily schedule
(versus the 28 day schedule).

CURRENTLY:
I have created a new spreadsheet that has already automated some of these
functions. I have created a cell so that I only have to enter one date and
then the sheet adds the remaining 28 days to the schedule. I added a row
that indicates for me if each day has the right number of people or not...

PROBLEM:
I created a row for each sheet that attempts to calculate the number of
people scheduled for a particular day. The problem in the calculation is
that we always have part-time, as needed and float personnel that work on
various units. This group of individuals is used to augment a units staff.
The formula I'm using is as follows (this formula is for example in cell B20:

=IF(B7="L",1,IF(B8="L",1,IF(B9="L",1,IF(B10="L",1, IF(B11="L",1,IF(B12="L",1,IF(B13="L",1,IF(B14="L", 1,0))))))))

The "L" stands for the unit the employee is assigned to work, other values
in B7:B14 could be "J" or "W" to designate our other units.

This formula works great if there is only one part-time, as needed or float
person scheduled for a particular unit, but on some days, we do have more
than one person. Which then makes the daily calculation:

=IF(SUM(B18:B20)<2,"X","")

Incorrect because my formula can only count one or zero, it cannot count
two. Can you tell me how I can adjust the formula so that it would count all
nurses B7:B14 with a certain letter designation ("L" in this case).

Thanks in advance for your help. I don't see how to attach the spreadsheet
so you can see the whole function, so I've done my best to describe. I am
really just a novice user.

=IF(B7="L",1,IF(B8="L",1,IF(B9="L",1,IF(B10="L",1, IF(B11="L",1,IF(B12="L",1,IF(B13="L",1,IF(B14="L", 1,0))))))))

Simplify version:

=SIGN(COUNTIF(A7:A14,"L"))


"Pat" wrote:

BACKGROUND:
I'm trying to create a spreadsheet that would help me do the monthly
schedule for our nurses more efficiently. Right now, the spreadsheet is just
fully manual, add all the dates (to six sheets), review the schedules cell by
cell to make sure we have enough people scheduled and then take the daily
schedules and apply to yet another spreadsheet that shows the daily schedule
(versus the 28 day schedule).

CURRENTLY:
I have created a new spreadsheet that has already automated some of these
functions. I have created a cell so that I only have to enter one date and
then the sheet adds the remaining 28 days to the schedule. I added a row
that indicates for me if each day has the right number of people or not...

PROBLEM:
I created a row for each sheet that attempts to calculate the number of
people scheduled for a particular day. The problem in the calculation is
that we always have part-time, as needed and float personnel that work on
various units. This group of individuals is used to augment a units staff.
The formula I'm using is as follows (this formula is for example in cell B20:

=IF(B7="L",1,IF(B8="L",1,IF(B9="L",1,IF(B10="L",1, IF(B11="L",1,IF(B12="L",1,IF(B13="L",1,IF(B14="L", 1,0))))))))

The "L" stands for the unit the employee is assigned to work, other values
in B7:B14 could be "J" or "W" to designate our other units.

This formula works great if there is only one part-time, as needed or float
person scheduled for a particular unit, but on some days, we do have more
than one person. Which then makes the daily calculation:

=IF(SUM(B18:B20)<2,"X","")

Incorrect because my formula can only count one or zero, it cannot count
two. Can you tell me how I can adjust the formula so that it would count all
nurses B7:B14 with a certain letter designation ("L" in this case).

Thanks in advance for your help. I don't see how to attach the spreadsheet
so you can see the whole function, so I've done my best to describe. I am
really just a novice user.

Thank you for your help, your formula wasn't quite right for my purposes, but
it did put me on track to find the correct and simplified formula for my
purposes which was:

=COUNTIF(B7:B14,"L")

Thanks again for your help Teethless Mama.

"Teethless mama" wrote:


=IF(B7="L",1,IF(B8="L",1,IF(B9="L",1,IF(B10="L",1, IF(B11="L",1,IF(B12="L",1,IF(B13="L",1,IF(B14="L", 1,0))))))))

Simplify version:

=SIGN(COUNTIF(A7:A14,"L"))


"Pat" wrote:

BACKGROUND:
I'm trying to create a spreadsheet that would help me do the monthly
schedule for our nurses more efficiently. Right now, the spreadsheet is just
fully manual, add all the dates (to six sheets), review the schedules cell by
cell to make sure we have enough people scheduled and then take the daily
schedules and apply to yet another spreadsheet that shows the daily schedule
(versus the 28 day schedule).

CURRENTLY:
I have created a new spreadsheet that has already automated some of these
functions. I have created a cell so that I only have to enter one date and
then the sheet adds the remaining 28 days to the schedule. I added a row
that indicates for me if each day has the right number of people or not...

PROBLEM:
I created a row for each sheet that attempts to calculate the number of
people scheduled for a particular day. The problem in the calculation is
that we always have part-time, as needed and float personnel that work on
various units. This group of individuals is used to augment a units staff.
The formula I'm using is as follows (this formula is for example in cell B20:

=IF(B7="L",1,IF(B8="L",1,IF(B9="L",1,IF(B10="L",1, IF(B11="L",1,IF(B12="L",1,IF(B13="L",1,IF(B14="L", 1,0))))))))

The "L" stands for the unit the employee is assigned to work, other values
in B7:B14 could be "J" or "W" to designate our other units.

This formula works great if there is only one part-time, as needed or float
person scheduled for a particular unit, but on some days, we do have more
than one person. Which then makes the daily calculation:

=IF(SUM(B18:B20)<2,"X","")

Incorrect because my formula can only count one or zero, it cannot count
two. Can you tell me how I can adjust the formula so that it would count all
nurses B7:B14 with a certain letter designation ("L" in this case).

Thanks in advance for your help. I don't see how to attach the spreadsheet
so you can see the whole function, so I've done my best to describe. I am
really just a novice user.