Formula Tweak Rework

[robcarr]

Hi,

I am using the formula below to calculate number of people in on any given hour

=SUM((AND($K5>=$A$4,$K5<=$B$4)*E$4),(AND($K5>=$A$3,$K5<=$B$3)*E$3),(AND($K5>=$A$5,$K5<=$B$5)*E$5),(AND($K5>=$A$6,$K5<=$B$6)*E$6),(AND($K5>=$A$7,$K5<=$B$7)*E$7),(AND($K5>=$A$8,$K5<=$B$8)*E$8),(AND($K5>=$A$9,$K5<=$B$9)*E$9),(AND($K5>=$A$10,$K5<=$B$10)*E$10),(AND($K5>=$A$11,$K5<=$B$11)*E$11),(AND($K5>=$A$12,$K5<=$B$12)*E$12),(AND($K5>=$A$13,$K5<=$B$13)*E$13),(AND($K5>=$A$14,$K5<=$B$14)*E$14),(AND($K5>=$A$15,$K5<=$B$15)*E$15),(AND($K5>=$A$16,$K5<=$B$16)*E$16),(AND($K5>=$A$17,$K5<=$B$17)*E$17),(AND($K5>=$A$18,$K5<=$B$18)*E$18),(AND($K5>=$A$19,$K5<=$B$19)*E$19),(AND($K5>=$A$20,$K5<=$B$20)*E$20),(AND($K5>=$A$21,$K5<=$B$21)*E$21),(AND($K5>=$A$22,$K5<=$B$22)*E$22),(AND($K5>=$A$23,$K5<=$B$23)*E$23),(AND($K5>=$A$24,$K5<=$B$24)*E$24))+V5

this has been setup to calculate 22 different times and tell me how many people are in on each hour of the day based on the shift they are on.

Is it possible to improve the formula so I can easily add extra times in colums A and B without having to change the formula each time, as i have this formula 182 times on a sheet.

column A and b have start and end times of shift.

columns D thru J have the days and number of people on each shift.
column v is any extra values - manual entry.



Hope this is of use, Rob.

 

[GlennUK]

It looks like this might do it:

=SUMPRODUCT(($K5>=$A$4:$A$24)*($K5<=$A$4:$A$24)*($E$4:$E$24))+V5

Can you try it and see if it works?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Good call Glenn, but just another tweek...
[tt]
=SUMPRODUCT(($K5>=$A$3:$A$24)*($K5<=$B$3:$B$24)*($E$3:$E$24))+$V5
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

D'oh, well spotted Skip. I'd assumed that A4 was the first cell.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

hi,

Have tried the forumla and it works nearly, it falls over if the start time is on a 1/4 hour.

It also will not pick up in any part 13:45 - 19:15 time slot for some reason.

I cant figure out why it does it, I have even tried to set the K5 cells to go in 15 minute intervals and still no joy.



Hope this is of use, Rob.

 

[GlennUK]

Show the formula that you have. And can you give an example of the contents of a cell that has a start time on 1/4 hour, please? This sounds like data problems rather than logic.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

I have figured out why I just dont know how to get it to show

if the start time is either :15 or :45, it adds this to the next time slot and not the time slot it falls in,

for example

9 agents start at 13:45, this will show in the 14:00 slot and not in the 13:30, for ease of looking it would be better to see it in the 13:30 as this slot goes upto 13:59

the reason 13:45 didnt seem to register was becuise I had a finish time of 13:30, so it was calcualting I just didnt pick up intially.

Hope this is of use, Rob.

 

[GlennUK]

Without seeing your layout and data, it's hard to work out what's going on. Can you provide more information?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

i cant upload because of the data on the report.



Hope this is of use, Rob.

 

[robcarr]

glennuk,

"Show the formula that you have. And can you give an example of the contents of a cell that has a start time on 1/4 hour, please? This sounds like data problems rather than logic. "

I have used the formula posted and it works to a point.

all values in Column A are time formatted.

It doesnt seem to be a data issue, more that the formula is not adding the :15 and :45 into the correct time slot.



Hope this is of use, Rob.

 

[GlennUK]

You say that the formula is:

=SUMPRODUCT(($K5>=$A$3:$A$24)*($K5<=$B$3:$B$24)*($E$3:$E$24))+$V5

What is in K5?
What is in A3:A24?
What is in B3:B24?
What is in E3:E24?
What is in V5?

What result do you get?
What result do you actually expect?


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

hi,

k5 has the time I am checking in half hour intervals K5:k30
a3:a24 has all the start times for all shifts
b3 b24 has all the end times for all the shifts.
e3:e24 is the number of people on each shift
v5 is irrelevant as this is a manual number which isnt used at the moment.

k, a and B are all formatted as time hh:mm:ss

if a shfit starts at 09:45, it adds this shift to the 10:00 times slot and not the 09:30 slot, the formula seems to round up the time.




Hope this is of use, Rob.

 

[GlennUK]

See if this makes sense:

=SUMPRODUCT(($A$3:$A$24>=$K5)*($A$3:$A$24<=$K6)*($E$3:$E$24))

This compares the 2 slot-times at the beginning and end of a slot against the start-time. Note that K5 and K6 are used in the comparison, for start and end, so you will need a final end time after the last formula.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

By the way, you may want to alter either the >= or the <= so that times on the boundaries don't get counted twice.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[byundt]

One of the problems with shift worker counting is the possibility that rounding discrepancies may make the binary fraction equivalent to a time like 14:15 not exactly equal to the time under consideration. This might occur if the times in column K are generated by filling a series down, while those in columns A and B are manually entered.

As a workaround, I like to add or subtract a very small amount to make sure that people who are on duty at a given time are correctly counted.

The following formula tells you how many people are on duty at the specific time given by cell K5. It requires that the shift finish time be on or after K5, and the shift start time to be on or before K5.
=SUMPRODUCT(($B$3:$B$24>=($K5-0.0001))*($A$3:$A$24<=($K5+0.0001)),$E$3:$E$24)+V5
The 0.0001 in the above formula is equivalent to 8.64 seconds.

Brad