Keep sum at 0 and to color change when above?

[csroberts]

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!

 

[Saba Sabaratnam]

You could the following formula in A3 to achieve this outcome

=MAX(A1-A2,0)

Then use conditional formatting to color anything above zero.

Kind regards

Saba

 

[csroberts]

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.

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]

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)

Kind regards

Saba

 

[csroberts]

Perfect! It worked! Thank you so much!