[Solved] Do Not Include if Value Less Than Zero??

Discuss the spreadsheet application
Post Reply
HartsCottage
Posts: 3
Joined: Fri Sep 17, 2021 3:14 am

[Solved] Do Not Include if Value Less Than Zero??

Post by HartsCottage »

Hello!

Been reading these boards for a while in search for answers as I learn how to use the better programs found in the suite, but I seem to be at a loss finding an answer for this one:

I'm calculating daily time sheets as a CYA method with my job, and I've used these sheets to keep track of production, time, hours-paid, bonuses, etc. as well. They've worked great, but I have one calculation yet that I can't seem to correctly include.

By contract, working less than 8 hours still guarantees a pay for that day of 8 hours. When I work 8.01 hours, the formulas work just fine. If I work 7.98 hours, the formulas calculating standard daily pay don't show 8 hours -- they show the resulting pay for 7.98 hours. How does one go about telling a cell to calculate normally unless the multiple is less than 8.00, in which case the formula should then assume 8.00 for that variable?

In addition, if I may squeeze it in, the resulting Overtime calculations for the less-than-8.00 day show a negative number, and of course it should always be positive or zero. Is this simply a matter of specifying ABSOLUTE in the formula to the cells calculating Overtime Hours?
Last edited by HartsCottage on Sat Sep 18, 2021 5:04 am, edited 1 time in total.
OpenOffice 4.1.10 on Winduhs 10 Home x64
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Do Not Include if Value Less Than Zero??

Post by FJCC »

One way to force a calculation to use 8 hours is to replace the cell reference with the MAX() function. For example, if you have the formula

Code: Select all

=C2 * $D$1
where C2 holds the number of hours and D1 holds the hourly rate, you can replace that with

Code: Select all

=MAX(C2;8) * $D$1
If C2 is less than 8, the MAX function will return 8. If C2 is greater than 8, it will return the value of C2.
The problem with negative numbers may be due to time worked on different days but we can't really tell without seeing your data. Can you upload a small file demonstrating the problem? To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
HartsCottage
Posts: 3
Joined: Fri Sep 17, 2021 3:14 am

Re: Do Not Include if Value Less Than Zero??

Post by HartsCottage »

This is the file you requested. Note on Tuesday, 14 September, number in E29 is less than 8.00, which makes the number in E30 negative, which results in an incorrect (negative) dollar value for E32. These values shouldn't cross days, which are arranged so each 4-column group is one day of the work week.

Ideally, the Real Value in E29 should still display (7.57) while the formula for that cell would assume a value of 8.00.

Thanks!
Attachments
Sample Under-8 CYA Card.ods
(27.91 KiB) Downloaded 83 times
OpenOffice 4.1.10 on Winduhs 10 Home x64
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Do Not Include if Value Less Than Zero??

Post by FJCC »

If you change the formula in E30 to

Code: Select all

=MAX(E29-8;0)
then it will be 0 in those cases where the hours worked are less than 8.

I am not sure what you mean by
"Ideally, the Real Value in E29 should still display (7.57) while the formula for that cell would assume a value of 8.00."
The formula in E31 does seem to assume 8 hours while E29 shows 7.57.

By the way, there is no need to wrap calculations like E29 - 8 in the SUM function. SUM(E29 - 8) returns the same answer as E29 -8.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
HartsCottage
Posts: 3
Joined: Fri Sep 17, 2021 3:14 am

Re: Do Not Include if Value Less Than Zero??

Post by HartsCottage »

Well, that seems to have solved the issue. Thanks so much for the help. Daily records will be a whoooole lot easier when this tool functions correctly.

As far as
"I am not sure what you mean by
"Ideally, the Real Value in E29 should still display (7.57)..."

It does what I want it to -- I'd forgotten that the formula I'm referencing already assumes 8 hours in the day and the necessary cell will still display the total actual hours worked -- a consequence of getting too little sleep.

In reference to the SUM function, I've copied these sheets from an Excel file that hasn't been used for nearly ten years and that's how the function was laid out in that sheet. But it works, so I haven't changed all the old formulas to cleaner versions.

Thanks again for the help.
OpenOffice 4.1.10 on Winduhs 10 Home x64
Post Reply