Time Tracker

So I'm at work this evening, and I have a Monday deadline to complete a time tracker.

In a nutshell, we have to meet a quota of 11 monitors a day.

For every 40 minutes of meetings, 1 monitor is deducted, (because the meetings take away from our monitoring time)

So if I have 80 minutes worth of meetings, 2 monitors will be deducted from 11, and leave me with 9 monitors.

I have it working perfect, but only when the amount of meeting minutes equals increments of 40:
40 mins deducts 1
80 mins deducts 2
120 deducts 3

And so on.

Currently I have my If statements: =IF(B149=40,"1"), IF(B149=80,"2"), etc.

So if I have 40 total meeting minutes, I get a 1 and I just deduct that from my 11, making 10.

However; If I work 50, 60, or 70 minutes, It reverts back to 11 monitors until I get to 80 minutes then it changes to 9, (When I get to 120, it changes to 8, etc.)

I need to figure out a formula that will keep the first "1" deduction from 11, (keeping it at 10) anywhere in the 40-79 range until I get to 80, and change it, etc.

I'm so close but I can't figure out how to get my If statement to keep the "10" as is until I get to 80 minutes, then change to "9"

Help? I wish I had examples, but not at work right now. I am hoping to get something before Monday!

Thanks all.

Angela (excellfully inclined)

Originally Posted by pjoaquin

Hi preatygirl,

Try this:

Please Login or Register  to view this content.

That sounds good Paul....

And sorry to the first replyer, I really don't have an example.

Other than the total amount of monitors calculates in cell B149 as soon as the number of hours is inputed, (8 hours = 11 monitors)

I have it deducting 1 monitor per 40 minutes, (it's all tracked in intervals of 10 minutes) so my problem is, at 50, 60, 70 minutes, it reverts back to 11 monitors, (instead of staying at 10) and so on.

So when I hit 80, it goes to 9 (as it should) but on 90, 100, 110 it reverts back to 11, until I get to 160. (every 40 mins)

Hope this makes better sense.

I will try the above formula Monday at work and see what happens, any other suggestions are much appreciated.

Thanks!