Sumproduct Adjustment
 

Hi there --

I have an extensive Excel spreadsheet that requires SUMPRODUCT in many of my
formulas. I have had success so far, but am now stumped. Any help would be
appreciated.

Here's a sample layout of the portion of the spreadsheet I'm calculating from:

Col A Col B (hours) Col C (bill rate)
FT 46 $95
FT-OT 48 $85
FT-OT 40 $75
FT 36 $65

So here's the issue:
1. If Col A is FT (full time), simply multiple Col B by Col C.
2. If Col A is FT-OT (overtime allowed) and over 40 hours, multiple 40
times Col C, multiple hours over 40 times (Col C * 1.5) for time-and-a-half.
3. If Col A is FT-OT and <= to 40, simply multiple Col B by Col C.

1 and 3 are not a problem at all. I can handle that. However, my problem is
with 2. It is a bit complicated communicating what I've tried, so I won't
include that here. But if anyone could provide the formula to accomplish
this, I would be VERY APPRECIATIVE.

Thanks so much. Let me know if you need any additional info.

Ellen

Sumproduct Adjustment
 

I'd think about it in two pieces that get added together. The first is just
rate*hours. The second, the overtime premium, is "IF the type if FT-OT, AND
the hours40, (hours-40)*rate/2.

So if your range extends to row 100, then the formula is

=sumproduct(b2:b100,c2:c100) +
sumproduct(--(a2:a100="FT-OT"),--(b2:b10040),(b2:b100-40),(c2:c100))/2.

Sumproduct Adjustment
 

=IF(AND(A1="FT-OT",B140),40*C1+(B1-40)*1.5*C1,B1*C1)

Greetings from New Zealand
"Ellen G" wrote in message
...
Hi there --

I have an extensive Excel spreadsheet that requires SUMPRODUCT in many of
my
formulas. I have had success so far, but am now stumped. Any help would be
appreciated.

Here's a sample layout of the portion of the spreadsheet I'm calculating
from:

Col A Col B (hours) Col C (bill rate)
FT 46 $95
FT-OT 48 $85
FT-OT 40 $75
FT 36 $65

So here's the issue:
1. If Col A is FT (full time), simply multiple Col B by Col C.
2. If Col A is FT-OT (overtime allowed) and over 40 hours, multiple 40
times Col C, multiple hours over 40 times (Col C * 1.5) for
time-and-a-half.
3. If Col A is FT-OT and <= to 40, simply multiple Col B by Col C.

1 and 3 are not a problem at all. I can handle that. However, my problem
is
with 2. It is a bit complicated communicating what I've tried, so I won't
include that here. But if anyone could provide the formula to accomplish
this, I would be VERY APPRECIATIVE.

Thanks so much. Let me know if you need any additional info.

Ellen

Sumproduct Adjustment
 

PERFECT! That is exactly what I needed. I was trying to overcomplicate the
formula. My brain simply wasn't computing the rate/2. THANK YOU SO MUCH!!!

Ellen

"bpeltzer" wrote:

I'd think about it in two pieces that get added together. The first is just
rate*hours. The second, the overtime premium, is "IF the type if FT-OT, AND
the hours40, (hours-40)*rate/2.

So if your range extends to row 100, then the formula is

=sumproduct(b2:b100,c2:c100) +
sumproduct(--(a2:a100="FT-OT"),--(b2:b10040),(b2:b100-40),(c2:c100))/2.

Sumproduct Adjustment
 

Thanks, Bill. But I'm afraid that due to other aspects of my formula, I must
use SUMPRODUCT. See bpeltzer's response -- his formula is exactly what I need.

Thanks again for taking the time to respond.

Ellen

"Bill Kuunders" wrote:

=IF(AND(A1="FT-OT",B140),40*C1+(B1-40)*1.5*C1,B1*C1)

Greetings from New Zealand
"Ellen G" wrote in message
...
Hi there --

I have an extensive Excel spreadsheet that requires SUMPRODUCT in many of
my
formulas. I have had success so far, but am now stumped. Any help would be
appreciated.

Here's a sample layout of the portion of the spreadsheet I'm calculating
from:

Col A Col B (hours) Col C (bill rate)
FT 46 $95
FT-OT 48 $85
FT-OT 40 $75
FT 36 $65

So here's the issue:
1. If Col A is FT (full time), simply multiple Col B by Col C.
2. If Col A is FT-OT (overtime allowed) and over 40 hours, multiple 40
times Col C, multiple hours over 40 times (Col C * 1.5) for
time-and-a-half.
3. If Col A is FT-OT and <= to 40, simply multiple Col B by Col C.

1 and 3 are not a problem at all. I can handle that. However, my problem
is
with 2. It is a bit complicated communicating what I've tried, so I won't
include that here. But if anyone could provide the formula to accomplish
this, I would be VERY APPRECIATIVE.

Thanks so much. Let me know if you need any additional info.

Ellen