Is there a way to get a function to return any result below 0 as 0 or not include negative numbers in sums? I'm using it to track my total overtime through the year at work. So right now I have it so if I work over 70hrs a week, say 85 it will return 15 hours. That works fine, but when I try to sum them all up I get -70 hours for each week I haven't worked yet.
Use the following formula to accomplish that:
EDIT 29-03-2013; 23:00 CEST
IMPORTANT: This is only valid if you work at least 70 hours per week, each week. It is just as important to know if you have worked less !! Therefore you need to change the way you calculate the "overtime". The following formula will show only a calculated value, if a "hours worked" value has been entered:
See example file, column E.
EDIT 29-03-2013; 22:30 CEST
The solution given by SpreadsheetPro.net works perfectly. The statement about the
The solution handed by Al, covers a different approach. Instead of calculating
There is a
It returns the highest value in a range of numbers. In this case,
You can also use
In your case something like:
This makes it more flexible if you want to have more than one criterion (as there is no SUMIFS function in Google Spreadsheet). In that case you can stack multiple criteria in the FILTER function.