nested if function

[Robb27]

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

 

[Biff]

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]

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

 

[Biff]

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

 

[Robb27]

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,
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

 

[Biff]

Ok, good deal! Thanks for the feedback!

Biff

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,
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