Cell needs IF AND, with SUM of different cells to IF AND cells - Confused? I am

[tammorice]

Hi

I have a table where $M11=IF(AND($K$3="CASUAL",$F11<TIMEVALUE("02:00"),$F11>TIMEVALUE("00:00")),"02:00",$F11)

	F	I	L	M
	shift time	break	break	total paid
11	8:30	0:30	0:15	7:45
12	1:45			2:00

<tbody>
</tbody>

K3 "CASUAL"

<tbody>
</tbody>

So all is good if the shift is less than 2hrs then the total paid is increased to 2hrs.

Any shift can be recorded on any row. so I need to be able to also deduct breaks from the total.

Can anyone guide me on how I can add the sum SUM(F11-I11-L11) to the formula above in M11:M50

Super appreciate your help.

 

[steve the fish]

Im not entirely sure what you are after but perhaps try this:

=IF(AND($K$3="casual",F11>0),MAX(2/24,F11-I11-L11),0)

 

[tammorice]

Im not entirely sure what you are after but perhaps try this:

=IF(AND($K$3="casual",F11>0),MAX(2/24,F11-I11-L11),0)

Hey Steve the Fish, Thanks for such a quick response.

What I'm after is for these 2 formulas to calculate an answer in the one cell.

=IF(AND($K$3="CASUAL",$F11<TIMEVALUE("02:00"),$F11>TIMEVALUE("00:00")),"02:00",$F11)
=SUM(F11-I11-L11)

Row 11 is used for 2 different calcs.
1. a job (using drop down) with less than 2 hours (F11), that gets paid a minimum of 2hrs (M11), if more than 2 hours (F11), gets paid amount in F11.
2. a job (using drop down) with more than 2 hours (F11), that has breaks to be deducted (I11 and L11), that gets paid (shifthrs-breaks) (M11)

 

[tammorice]

Hey Steve the Fish, Thanks for such a quick response.

What I'm after is for these 2 formulas to calculate an answer in the one cell.

=IF(AND($K$3="CASUAL",$F11<timevalue("02:00"),$f11>TIMEVALUE("00:00")),"02:00",$F11)
=SUM(F11-I11-L11)</timevalue("02:00"),$f11>

Row 11 is used for 2 different calcs.
1. a job (using drop down) with less than 2 hours (F11), that gets paid a minimum of 2hrs (M11), if more than 2 hours (F11), gets paid amount in F11.
2. a job (using drop down) with more than 2 hours (F11), that has breaks to be deducted (I11 and L11), that gets paid (shifthrs-breaks) (M11)

I just realised my IF formula is wrong. oops

it should read:

=IF(AND($L$5="CASUAL",$F11<<timevalue("02:00"),$f11>TIMEVALUE("02:00"),$F11>TIMEVALUE("00:00")),"02:00",$F11)</timevalue("02:00"),$f11>

 

[steve the fish]

Did you try what i suggested? Does that not do what you ask for?

 

[tammorice]

Did you try what i suggested? Does that not do what you ask for?

No. Unfortunately, it returned "00:00".

It's time to have another look at this one (It was driving me crazy - so I backed away for a bit ) cause the basic vlookup i just did doesn't want to play either.

 

[steve the fish]

Thats because either K3 isnt 'casual' or F11 isnt > 0.