Hello everyone,

I am hoping someone can help make this a little easier for me to understand.

I have read through most if not all the previous posts on conditional
formating, SUMIF, etc...but I am no further ahead. It's probably simple,
which is why I am not getting it, but here goes....

I need to write a formula that will take the value of 'A' and if 'A' is
greater than 2, subtract 2 and multiple the remainder by 2, then add 3.

Part two or that same formule will say if 'A' is equal to or less than 2,
simply multiply by 1.5

What is boils down to is Overtime Pay Records where if the employee works 2
hours or less of overtime, then pay is at time and a half. If they work more
than 2 hours, the first 2 hours are at time and a half with the remainder at
double time.

Now, one more thing. If possible I would also like to vary the formula based
on 'B' being either 'R' or 'D'. if 'B' = "D" then the entire amount of 'A'
would be double time. If 'B' = "R", then the formula needed above would apply.

Thanks very much for any help.

Cheers!

"Paul B." wrote:
I have read through most if not all the previous posts on
conditional formating, SUMIF, etc...but I am no further ahead.
[....]
I need to write a formula that will take the value of 'A'
and if 'A' is greater than 2, subtract 2 and multiple the
remainder by 2, then add 3.

Part two or that same formule will say if 'A' is equal to
or less than 2, simply multiply by 1.5
[....]
Now, one more thing. If possible I would also like to
vary the formula based on 'B' being either 'R' or 'D'.
if 'B' = "D" then the entire amount of 'A' would be
double time. If 'B' = "R", then the formula needed
above would apply.

Not quite sure what this has to do with SUMIF(). Does
the following meet your needs:

=IF(B1 = "D", 2*A1,
IF(B1 < "R", A1, IF(A1 <= 2, 1.5*A1, (A1 - 2)*2 + 3)))

Note: I am guessing what that if B1 is neither "D" nor
"R", you simply want the value of A1

As I have written it, this formula would go into some cell
other than B1 or A1. Does that match your intent?

Try This:

Assuming that the data is in row 3, that column B has the "R" or "D",
and column A has the number of overtime hours, insert this into column
C:

=IF(B3="R",IF(A32,((A3-2)*2)+3,A3*1.5),IF(B3="D",A3*2,"FALSE"))

This will return the equivalent number of single hours to be paid in
column C and return a value of "FALSE" if neither R or D are found.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=498319

Thanks to both of you for you help....

One more thing if I may ask, how would to formula look it I were to add a
third variable of 'T' for triple time?

Cheers


"mphell0" wrote:


Try This:

Assuming that the data is in row 3, that column B has the "R" or "D",
and column A has the number of overtime hours, insert this into column
C:

=IF(B3="R",IF(A32,((A3-2)*2)+3,A3*1.5),IF(B3="D",A3*2,"FALSE"))

This will return the equivalent number of single hours to be paid in
column C and return a value of "FALSE" if neither R or D are found.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=498319

I am assuming that everyone with a "T" gets triple time for all hours of
overtime instead of everything but the first two hours. If that is the
case then the formula would look like this:

=IF(B3="R",IF(A32,((A3-2)*2)+3,A3*1.5),IF(B3="D",A3*2,IF(B3="T",A3*3,"FAL SE")))

If you need to add anything else just replace the "FALSE" with an IF
statement similar to the other ones.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=498319

Excellent, Thanks for the help and explainations!

"mphell0" wrote:


I am assuming that everyone with a "T" gets triple time for all hours of
overtime instead of everything but the first two hours. If that is the
case then the formula would look like this:

=IF(B3="R",IF(A32,((A3-2)*2)+3,A3*1.5),IF(B3="D",A3*2,IF(B3="T",A3*3,"FAL SE")))

If you need to add anything else just replace the "FALSE" with an IF
statement similar to the other ones.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=498319