Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
I currently have a function that I would like to tweak a little.
Here is the scenario: Example: Actual Hours Total Comptime Overtime AE AJ AL AM 169 180 12 0 Comp time is given for the first three Actual hours over 168. After 171 actual hours it goes to overtime. However, if total time exceeds 168 and actual hours does not exceed 171, I need it to calculate comptime for hours over 168. When I do this now it automatically goes to 3 in the AL column. Here is the formula currenty in use. =IF(AE6<171,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) Any ideas? It is confusing to even try to explain it let alone understand it. Thanks JP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
=IF(AND(AJ6168,AE6<171),MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
"JP" wrote: I currently have a function that I would like to tweak a little. Here is the scenario: Example: Actual Hours Total Comptime Overtime AE AJ AL AM 169 180 12 0 Comp time is given for the first three Actual hours over 168. After 171 actual hours it goes to overtime. However, if total time exceeds 168 and actual hours does not exceed 171, I need it to calculate comptime for hours over 168. When I do this now it automatically goes to 3 in the AL column. Here is the formula currenty in use. =IF(AE6<171,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) Any ideas? It is confusing to even try to explain it let alone understand it. Thanks JP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
You are a genius.
Thanks for the help "kassie" wrote: =IF(AND(AJ6168,AE6<171),MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) "JP" wrote: I currently have a function that I would like to tweak a little. Here is the scenario: Example: Actual Hours Total Comptime Overtime AE AJ AL AM 169 180 12 0 Comp time is given for the first three Actual hours over 168. After 171 actual hours it goes to overtime. However, if total time exceeds 168 and actual hours does not exceed 171, I need it to calculate comptime for hours over 168. When I do this now it automatically goes to 3 in the AL column. Here is the formula currenty in use. =IF(AE6<171,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) Any ideas? It is confusing to even try to explain it let alone understand it. Thanks JP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
My pleasure!
"JP" wrote: You are a genius. Thanks for the help "kassie" wrote: =IF(AND(AJ6168,AE6<171),MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) "JP" wrote: I currently have a function that I would like to tweak a little. Here is the scenario: Example: Actual Hours Total Comptime Overtime AE AJ AL AM 169 180 12 0 Comp time is given for the first three Actual hours over 168. After 171 actual hours it goes to overtime. However, if total time exceeds 168 and actual hours does not exceed 171, I need it to calculate comptime for hours over 168. When I do this now it automatically goes to 3 in the AL column. Here is the formula currenty in use. =IF(AE6<171,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168))) Any ideas? It is confusing to even try to explain it let alone understand it. Thanks JP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
How to use SUMIF function with non-adjacent cells | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |