Simple formula has me confused

[kidiowa]

OK, this has me confused. I have a formula I use at work to determine Nursing Hours Per Patient Day (NHPPD) which is basically the sum of employees *4 (a four hour block)/census (number of patients on the floor). This formula (i'll put below) if for the whole day.

=sum(B112:G112)*4/B115

Now my boss wants to determine NHPPD for every 4 hour block so I put in this and dragged across:

=(B112*4)/B115

where B112 is the total staff for that 4 hour period, the 4 is the 4 hour period it covers, and B115 is the census for that 4 hour period.

Here's my problem: when the census (B115) goes down or the staffing (B112) goes up, the NHPPD should go up but it doesn't. The formula is determining NHPPD, which "should" mean that the more staff (B112) or lower census (B115) would get you a higher NHPPD. Anyone know how to fix it? If my explanation needs further explanation, let me know. Thanks in advance.

 

[Ashhu]

Your logic is accurate, lower census or higher the staff which ideally increase the NHPPD.
the sample result of value or sheet will be helpful to analyse the excel formula. check if you can send.

 

[kidiowa]

That would probably have helped, sorry about that.

 

[Ashhu]

That would probably have helped, sorry about that.

Hi
numerator and denominator replaced in the formula, please check now.

 

[kidiowa]

Almost there. Each formula should have a block with a result somewhere in the 8-12 range for NHPPD. I'm thinking the multiplier (the *4 for the 4 hour block) should be higher to get the number there, but I don't know the justification for it. To see how the old way worked, in one of the cells off to the side put in this formula:

=SUM(B104:G104)*4/B113

In B113 put in 18, then change B113 to see how the results in the above formula work, that's the kind of result I'm needing for each 4 hour block. Sorry, pretty confusing.

 

[Shteven]

If you're trying to find the NHPPD per 4 hour block, you can show it one of two ways (depending on your preference).
1) Your denominator can be divided into 4 hour blocks, and therefore it would be (B104*4)/(B108/6).
2) Change your NHPPD average formula to a sum formula.

 

[candybg]

Just to add to what Shteven said, you need to divide your census for the block of time by 6 to represent that the block of time is only 1/6th of a day.