| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf for Variable Conditions
I have a brain teaser funtion needed for a time sheet.
Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf for Variable Conditions
"JP" wrote: I have a brain teaser funtion needed for a time sheet. Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did not say what you wanted to do if there was no comp time so had it return a zero. I hope this helps |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf for Variable Conditions
The numbers may fluctuate in each column. These are not going to be the set
numbers becasue times may differ. Also,if no comp time then zero. Thanks for the response JP "Chris Former Excel Support Professional" wrote: "JP" wrote: I have a brain teaser funtion needed for a time sheet. Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did not say what you wanted to do if there was no comp time so had it return a zero. I hope this helps |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf for Variable Conditions
I don't get why it is 12 in your example, but perhaps
=IF(AE6=171,AJ6-168,AE6-168) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JP" wrote in message ... The numbers may fluctuate in each column. These are not going to be the set numbers becasue times may differ. Also,if no comp time then zero. Thanks for the response JP "Chris Former Excel Support Professional" wrote: "JP" wrote: I have a brain teaser funtion needed for a time sheet. Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did not say what you wanted to do if there was no comp time so had it return a zero. I hope this helps |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf for Variable Conditions
Hi
Maybe =IF(AE1<168,0,AJ1-AE1+MIN(3,AJ1-168)) -- Regards Roger Govier "JP" wrote in message ... The numbers may fluctuate in each column. These are not going to be the set numbers becasue times may differ. Also,if no comp time then zero. Thanks for the response JP "Chris Former Excel Support Professional" wrote: "JP" wrote: I have a brain teaser funtion needed for a time sheet. Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did not say what you wanted to do if there was no comp time so had it return a zero. I hope this helps |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf for Variable Conditions
This is the exact answer to the problem
Thanks a bunch JP "Roger Govier" wrote: Hi Maybe =IF(AE1<168,0,AJ1-AE1+MIN(3,AJ1-168)) -- Regards Roger Govier "JP" wrote in message ... The numbers may fluctuate in each column. These are not going to be the set numbers becasue times may differ. Also,if no comp time then zero. Thanks for the response JP "Chris Former Excel Support Professional" wrote: "JP" wrote: I have a brain teaser funtion needed for a time sheet. Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did not say what you wanted to do if there was no comp time so had it return a zero. I hope this helps |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf for Variable Conditions
Hi
You're very welcome. Thanks for the feedback. -- Regards Roger Govier "JP" wrote in message ... This is the exact answer to the problem Thanks a bunch JP "Roger Govier" wrote: Hi Maybe =IF(AE1<168,0,AJ1-AE1+MIN(3,AJ1-168)) -- Regards Roger Govier "JP" wrote in message ... The numbers may fluctuate in each column. These are not going to be the set numbers becasue times may differ. Also,if no comp time then zero. Thanks for the response JP "Chris Former Excel Support Professional" wrote: "JP" wrote: I have a brain teaser funtion needed for a time sheet. Here is the set-up: Example Numbers: AE AJ AL Actual Hours Total Hours Comp Time 171 180 = 12 This is how comp time should be calculated. Here are the conditions: Comp time is given for the first three Actual Hours over 168. Up until 171 hours. For example, if actual hours is 171 and total hours is 180 than comp time should equal 12. Here is the current function I am using. However, when these numbers are pulgged in comp time stays at 3: =IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) How can I tweek this to work correctly? Many thanks JP Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did not say what you wanted to do if there was no comp time so had it return a zero. I hope this helps |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF with 2 conditions | Excel Worksheet Functions | |||
| SUMIF function with 2 conditions | Excel Worksheet Functions | |||
| SUMIF with two conditions | Excel Discussion (Misc queries) | |||
| sumif with two conditions | Excel Worksheet Functions | |||
| SUMIF based on 2 conditions | Excel Worksheet Functions | |||
Linear Mode
