Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Adjustment? | Excel Worksheet Functions | |||
formula adjustment | Excel Worksheet Functions | |||
Formula Adjustment - Help | Excel Worksheet Functions | |||
Inflation Adjustment | Excel Discussion (Misc queries) | |||
Copy without Adjustment | Excel Discussion (Misc queries) |