Help with 'IF' Formula/Circular Ref Error MSG Pls

[carol01]

Hello,

I'm very new to excel as you will see! I am trying to work out a timesheet & am having some trouble with the IF statement.

I have a column for Overtime hours & Another for Time & Half which Im working on now. Overtime hours will be either calculated in
the time 1/2 or dbl time columns. The first 2 hours or under will need to be calculated to J12, time 1/2 but how can I do this?

=IF(I12<=2,I12,J12) This is the formula I have used but its muddled since Its returning an error as circular ref & it wont calculate now
when I subtract.

Overtime T1/2
4.00 2.00
2.30 2.00
0.60 0.60
0.00 0.00
3.00 0.00
8.30 2.00
using formual above.

thank-you

[ericjs61]

Hey Carol01,

Make sure you are not entering the if statement in cell I12 or J12, if so, that is why you are getting the circular error.

Eric

[carol01]

Yep I realise that but Im unsure what to do to get the formula working Just need to see if someone here can find the formula which will work to calculate the time & half...

[ericjs61]

Let see if I can help...

Example:

You earn $18 per hour. Working 40 hours a week. However, you get paid over time. any hours over 40, you get time + half.

=(18*40) + (Total hours - 40)*(18*1.5)

A1= Hourly Rate, B1= Total Hours C1= Total Pay D1= Overtime Pay

C1=(A1*40)+(B1-40)*(A1*1.5)
D1=(Total hours-40)*(18*1.5)

I hope I understood you correctly.

[carol01]

thanks for that

All Im really doing atm is trying to find a formula that will subtract the <=2 from the overtime column & place that figure into the time1/2 column.
Example if J11 is 10.50 then in the Time1/2 column I need to have 2(being the max time1/2 figure.
Or if J11 is .50 then to have that show into the time 1/2.
When I use the formula I gave above it works-yay BUT I cant use it since it gives the circular ref & then it wont calculate
I should add these are being calculated daily..

If I can find a nested IF statement I think that will work since I have a couple of calculations for numbers showing in J11 over & under 2..

cheers

[carol01]

after struggling & trying this & that I seemed to have answered my own question & 'cracked' it!! lol.

thankyou ercicjs for trying to help me out-much appreciated!

formula I came up with :
=IF(I8<=2,I8,2)


Off for a much earned break now!