software

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' = quot;Dquot; then the entire amount of 'A'
would be double time. If 'B' = quot;Rquot;, then the formula needed above would apply.

Thanks very much for any help.

Cheers!
quot;Paul B.quot; wrote:
gt; I have read through most if not all the previous posts on
gt; conditional formating, SUMIF, etc...but I am no further ahead.
gt; [....]
gt; I need to write a formula that will take the value of 'A'
gt; and if 'A' is greater than 2, subtract 2 and multiple the
gt; remainder by 2, then add 3.
gt;
gt; Part two or that same formule will say if 'A' is equal to
gt; or less than 2, simply multiply by 1.5
gt; [....]
gt; Now, one more thing. If possible I would also like to
gt; vary the formula based on 'B' being either 'R' or 'D'.
gt; if 'B' = quot;Dquot; then the entire amount of 'A' would be
gt; double time. If 'B' = quot;Rquot;, then the formula needed
gt; above would apply.

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

=IF(B1 = quot;Dquot;, 2*A1,
IF(B1 lt;gt; quot;Rquot;, A1, IF(A1 lt;= 2, 1.5*A1, (A1 - 2)*2 3)))

Note: I am guessing what that if B1 is neither quot;Dquot; nor
quot;Rquot;, 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 quot;Rquot; or quot;Dquot;,
and column A has the number of overtime hours, insert this into column
C:

=IF(B3=quot;Rquot;,IF(A3gt;2,((A3-2)*2) 3,A3*1.5),IF(B3=quot;Dquot;,A3*2,quot;FALSEquot;))

This will return the equivalent number of single hours to be paid in
column C and return a value of quot;FALSEquot; if neither R or D are found.--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=498319Thanks 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?

Cheersquot;mphell0quot; wrote:

gt;
gt; Try This:
gt;
gt; Assuming that the data is in row 3, that column B has the quot;Rquot; or quot;Dquot;,
gt; and column A has the number of overtime hours, insert this into column
gt; C:
gt;
gt; =IF(B3=quot;Rquot;,IF(A3gt;2,((A3-2)*2) 3,A3*1.5),IF(B3=quot;Dquot;,A3*2,quot;FALSEquot;))
gt;
gt; This will return the equivalent number of single hours to be paid in
gt; column C and return a value of quot;FALSEquot; if neither R or D are found.
gt;
gt;
gt; --
gt; mphell0
gt; ------------------------------------------------------------------------
gt; mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
gt; View this thread: www.excelforum.com/showthread...hreadid=498319
gt;
gt;


I am assuming that everyone with a quot;Tquot; 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=quot;Rquot;,IF(A3gt;2,((A3-2)*2) 3,A3*1.5),IF(B3=quot;Dquot;,A3*2,IF(B3=quot;Tquot;,A3*3,quot;FAL SEquot;)))

If you need to add anything else just replace the quot;FALSEquot; with an IF
statement similar to the other ones.--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=498319Excellent, Thanks for the help and explainations!

quot;mphell0quot; wrote:

gt;
gt; I am assuming that everyone with a quot;Tquot; gets triple time for all hours of
gt; overtime instead of everything but the first two hours. If that is the
gt; case then the formula would look like this:
gt;
gt; =IF(B3=quot;Rquot;,IF(A3gt;2,((A3-2)*2) 3,A3*1.5),IF(B3=quot;Dquot;,A3*2,IF(B3=quot;Tquot;,A3*3,quot;FAL SEquot;)))
gt;
gt; If you need to add anything else just replace the quot;FALSEquot; with an IF
gt; statement similar to the other ones.
gt;
gt;
gt; --
gt; mphell0
gt; ------------------------------------------------------------------------
gt; mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
gt; View this thread: www.excelforum.com/showthread...hreadid=498319
gt;
gt;