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

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

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

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

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

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