Keep sum at 0 and to color change when above?

csroberts

New Member
Joined
Aug 2, 2020
Messages
5
Office Version
  1. 2016
Hello there,

I am looking for a way to keep a cell's formula equation above or equal to 0 AND condition it to change color above 0.01, in example:

I want A3 formula to subtract A2 [10.50] from A1 [3.50], in which it [A3] would equal [-7.00], so to keep it at 0, NOT -7.00 (without switching A2 with A1 in the formula) AND be able to color change [A3] when the equation is above 0.01 (given A1's number is higher than A2's).

OR

Keep A3 at 0.00 if lower than 40.00 AND color change when above 0.01

Basically, I am calculating hours in A3's column based on A2's column subtracting A1's column, and dont want A3's column to drop below 0.00 as it is an over time hours column and still be able to color change when above 0.01. A2 = base hours, A1 = time card hours, and A3 = over time hours (based on a 40 hour work week) - keeping A3 at 0.00 unless A2-A1 equaled above 40.

It's probably a lot simpler than I know, so if theres any help out there or if someone can point me to a thread with a smiliar question, that would be great. I would be more than happy to upload pictures for referencing.

Thank you so much!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
You could the following formula in A3 to achieve this outcome

1623957449882.png


=MAX(A1-A2,0)

Then use conditional formatting to color anything above zero.

1623957568481.png


Kind regards

Saba
 

csroberts

New Member
Joined
Aug 2, 2020
Messages
5
Office Version
  1. 2016
Thanks for your reply.

I have tried out your formula a few times and I am not getting my results... Maybe I am explaining this wrong, so let me rephrase entirely with an image.

othours.PNG


As you can see in B are the average hours, C is the actual hours, D is over time, E is Formula Value (the value from F columns formulas) and F is for the formula used.

Jim shouldn't have OT (D2, 0) because he doesn't work over 40 hours, but your formula (F2, =MAX(0,C2-B2)) shows 1 (E2, 1), but I typed it as 0, as I would like to see.

Jimmy and Jimbo both got 2 (D3, D4) hours over time, as I show in Jimbo's OT (D4) is conditioned to change color when above 1, even with minus avg (F3) or minus 40 (F4). So D3 would show 0 if C3 was 40, with both formulas (F3 and F4).

But Jeff, that lousy no-good-for-nothing, was late 1 hour, 3 days in a row. As per the formula (F5, =C5-40 {40 based on full hours}), it shows -3 (DE), but I typed it as 0, as I would like to see.

Maybe the avg. column isn't neccessary in the formula, but regardless of formula used or tried, I want OT column (D2 to D5) to show 0 until hours are over 40.

Does this make sense?
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
Hi,

Based on your explanation, I understand that you want to calculate OT only if an employee works above 40 hours.If so, you can use the following formula to get your result.

Enter the following formula in D2 and copy it down.

=iF(C2>40,C2-40,0)

1624048584776.png



Kind regards

Saba
 
Solution

Forum statistics

Threads
1,144,522
Messages
5,724,831
Members
422,582
Latest member
Vaibhav Wadhwa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top