Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested if function
Hello Everyone,
I have a nested IF function question. I have 9 columns. The first column (A) is the name of an employee. The 2nd (B) -8th (H) columns are days of the week. The 9th column is for tallying PTO hours (paid time off). For example - Normally there is an S (swing shift) in each cell of row 225, but if the employee wants to take PTO they replace the S with PTO. In column 9 (I225 I have a beginning number...say 40 (hours of accrued PTO) in I246 I have this formula: (equal sign) IF(B225="PTO",I225-8,I225+0.44,IF(C225=PTO,I225-8,I225+0.44,IF(D225=PTO,I225-8,I225+0.44,IF(E225=PTO,I225-8,I225+0.44 continue 3 more times))))))) I246 should look at I225 for the base number then look at row 225 for any PTO's. It should subtract 8 hours from the number in I225 for every PTO it finds, and it should add 0.44 (per day PTO accrual rate) for every S it finds. I know you can't have more than 7 IF functions in a nest, so this should work out great, but it doesn't. The first IF statement works,(by itself) but once you add the other IF's then it gives me an error. Thank you in advance for any help. Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested if function
Hi!
Try this one of these: =I255-COUNTIF(B255:H255,"PTO")*8+COUNTIF(B255:H255,"S")* 0.44 =I255-SUM(COUNTIF(B255:H255,{"PTO","S"})*{8,-0.44}) Biff "Robb27" wrote in message ... Hello Everyone, I have a nested IF function question. I have 9 columns. The first column (A) is the name of an employee. The 2nd (B) -8th (H) columns are days of the week. The 9th column is for tallying PTO hours (paid time off). For example - Normally there is an S (swing shift) in each cell of row 225, but if the employee wants to take PTO they replace the S with PTO. In column 9 (I225 I have a beginning number...say 40 (hours of accrued PTO) in I246 I have this formula: (equal sign) IF(B225="PTO",I225-8,I225+0.44,IF(C225="PTO",I225-8,I225+0.44,IF(D225="PTO",I225-8,I225+0.44,IF(E225="PTO",I225-8,I225+0.44 continue 3 more times))))))) I246 should look at I225 for the base number then look at row 225 for any PTO's. It should subtract 8 hours from the number in I225 for every PTO it finds, and it should add 0.44 (per day PTO accrual rate) for every S it finds. I know you can't have more than 7 IF functions in a nest, so this should work out great, but it doesn't. The first IF statement works,(by itself) but once you add the other IF's then it gives me an error. Thank you in advance for any help. Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested if function
Biff,
Thanks for the assistance. But, its a bit complicated for me. <insert sheepish grin here Im probably over-complicating it. An employee earns 6.16 hours of PTO every 2 weeks, so¦ can the formula maybe just add that number to the previous 2 weeks amount AND subtract 8 hours when they put PTO in the cell? Rob "Biff" wrote: Hi! Try this one of these: =I255-COUNTIF(B255:H255,"PTO")*8+COUNTIF(B255:H255,"S")* 0.44 =I255-SUM(COUNTIF(B255:H255,{"PTO","S"})*{8,-0.44}) Biff "Robb27" wrote in message ... Hello Everyone, I have a nested IF function question. I have 9 columns. The first column (A) is the name of an employee. The 2nd (B) -8th (H) columns are days of the week. The 9th column is for tallying PTO hours (paid time off). For example - Normally there is an S (swing shift) in each cell of row 225, but if the employee wants to take PTO they replace the S with PTO. In column 9 (I225 I have a beginning number...say 40 (hours of accrued PTO) in I246 I have this formula: (equal sign) IF(B225="PTO",I225-8,I225+0.44,IF(C225="PTO",I225-8,I225+0.44,IF(D225="PTO",I225-8,I225+0.44,IF(E225="PTO",I225-8,I225+0.44 continue 3 more times))))))) I246 should look at I225 for the base number then look at row 225 for any PTO's. It should subtract 8 hours from the number in I225 for every PTO it finds, and it should add 0.44 (per day PTO accrual rate) for every S it finds. I know you can't have more than 7 IF functions in a nest, so this should work out great, but it doesn't. The first IF statement works,(by itself) but once you add the other IF's then it gives me an error. Thank you in advance for any help. Rob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested if function
"Robb27" wrote in message
... Biff, Thanks for the assistance. But, it's a bit complicated for me. <insert sheepish grin here I'm probably over-complicating it. An employee earns 6.16 hours of PTO every 2 weeks, so. can the formula maybe just add that number to the previous 2 weeks amount AND subtract 8 hours when they put PTO in the cell? Rob Well, now I'm not following you. Either of those formulas does exactly what you asked for. add that number [6.16] to the previous 2 weeks But how did you calculate the total for the previous 2 weeks? Biff |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested if function
Thank you Biff. The light bulb came on after I worked with your formula for
awhile. I can see how it works now. I was right, I was making it more complicated than necessary. Rob "Biff" wrote: "Robb27" wrote in message ... Biff, Thanks for the assistance. But, it's a bit complicated for me. <insert sheepish grin here I'm probably over-complicating it. An employee earns 6.16 hours of PTO every 2 weeks, so. can the formula maybe just add that number to the previous 2 weeks amount AND subtract 8 hours when they put PTO in the cell? Rob Well, now I'm not following you. Either of those formulas does exactly what you asked for. add that number [6.16] to the previous 2 weeks But how did you calculate the total for the previous 2 weeks? Biff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested if function
Ok, good deal! Thanks for the feedback!
Biff "Robb27" wrote in message ... Thank you Biff. The light bulb came on after I worked with your formula for awhile. I can see how it works now. I was right, I was making it more complicated than necessary. Rob "Biff" wrote: "Robb27" wrote in message ... Biff, Thanks for the assistance. But, it's a bit complicated for me. <insert sheepish grin here I'm probably over-complicating it. An employee earns 6.16 hours of PTO every 2 weeks, so. can the formula maybe just add that number to the previous 2 weeks amount AND subtract 8 hours when they put PTO in the cell? Rob Well, now I'm not following you. Either of those formulas does exactly what you asked for. add that number [6.16] to the previous 2 weeks But how did you calculate the total for the previous 2 weeks? Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Nested "If" Function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |