Counting People Who Work Each Hour EXPLAINED

[Browun]

Hey all,

I've just started back into using excel and I am currently designing a timetable for a business. In this timetable I would like to be able to count the staff "coverage" of the shop and came across the forum post (http://www.excelforum.com/excel-form...each-hour.html) which provided a file and a working code to do this with.

HOWEVER!!
I am unsure on the purpose of some of the formula and was wondering if anyone would be kind enough to explain it to me so I can keep using excel with an understanding, rather than just grabbing other peoples work!
Here is the formula used and the original file respectively:

Please Login or Register  to view this content.

Copy of Template Drop Down with Coverage Chart-1.xls

Hope someone can help!

Thanks

[MarvinP]

Hi Browun and welcome to the forum.

What was this guy smoking when he wrote that formula???

Why would a person ever use Mod(.. , 1)??

So in this problem you need to add 1 if a person is working between two consecutive hours that are in Column B.

The first part of the formula in D29 is easy enough when it shows..

Please Login or Register  to view this content.

WAIT, WAIT... this returns True or False for all the D5 to D18 cells. You can't add or multiply True or false, they need to be turned into NUMBERS.

This is a common problem when using SumProduct and us normal Guru's change this True/False stuff to numbers by taking the negative or the negative of it. We use:

Please Login or Register  to view this content.

It looks like the formula you have used the MOD(.., 1) function to turn those things into numbers....

Try this formula in D29 instead of the one you have above and pull it down. Then test it by putting some start and end numbers in column D and E.

Please Login or Register  to view this content.

[Pete_UK]

No need for the double-minuses, Marvin - the multiplication operator (equivalent to AND for arrays) will coerce the True/False values to 1's or zeros.

I think the MOD(...,1) might be there to ensure that any dates among the times (and, indeed, any times over 24 hours which will count as 1 day plus) get omitted

Pete

[quekbc]

Additionally, for the OP, it's worth investigating the "Evaluate Formula" tool in Excel. You can find it in Formulas --> Evaluate Formula (in the Formula Auditing section)

Just select the cell with the formula, click on Evaluate Formula, and click on Evaluate button and see what happens.

[Browun]

Thank you all for the responses, they were all useful and I will definitely start using the "Evaluate Formula" tool, it's already helped me more than once!

I have sadly though hit another snag on my excel sheet and so if any of you would like to give your 2 cents that would be appreciated!

(http://www.excelforum.com/excel-form...ml#post4184183)

Thanks Again