Minusing a negative in an excel formula

[JasonTurbo]

Hey guys,

I am trying to build a spread sheet to keep track of my hours worked and to keep a record of wether I am up or down on hours over a given month. The problem I have is if I start a month with negative hours (hours owed to the company) my formula will not work correctly. My formula is this '=B41-(C6+(COUNT(B9:B39)*8))'

B41 is the hours that I have worked in the month minus C6 which is my time owed/time off plus the next bit of the formula counts all my hour inputs and works out what time I theoritically should of worked in that month.

If C6 is say -2.00 at the start of the month, even before I have input any data the formula is coming out with a positive 2.00 something to do with minusing a negative I think but I can't get my head round it. Any help will be muchly appreciated!

[tkowal]

Try minus-ing the absolute value .....

[MrShorty]

It looks to me like the overall logic of the formula is a little off. Let's work through it starting with your first month on the job (so there is no overage C6=0). I'm going to assume each month is 20 days=160 hours to simplify.

At the beginning of month 1, you need to work 160 hours (20*8+0) to "break even." Assume you work 158 hours. 158-160=-2, which tells me that a negative number denotes time you owe the company, a positive number denotes time the company owes you. C6 for month 2 = -2
At the beginning of month 2, you need to work 162 hours to break even [160 hours for month 2 + the 2 hours you didn't work in month 1. 162=160-(-2)]. This suggests that the error is in the (c6+count(...)*8) part of the function. I think if you make it (count(...)*8-c6) instead, your formula will work correctly.