Nested IF function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kimmer - 19 Apr 2008 01:08 GMT

I am having trouble in my excel class again. Nothing seems to be even close
to what we do in the book when we get to the assingments.
Need to enter a formula using the Nested If function to determine the number
of vacation days each employee is eligible for based on the employees job
status in column B and on the number of years employed in Column C.
criteria is: 17days for full-timeemployees who have worked more than 5 years
-- 12days for full-time employees who have worked more then 1 year
7 days for full-time employees who have worked 1 year or less
0 days for everyone else.
Very first person in the list has been FT for the least amount of time
Name Status yrs employed
A6 B6 C6 are the cells
Abba FT 0.1 data
I am getting totally lost. This is our last assignment and I am ready to
pull my hair out.

thanks to whoever can help.
Too old to be in college

Reply to this Message

Bill Kuunders - 19 Apr 2008 01:49 GMT

=IF(AND(B6="FT",C6>=5),17,IF(AND(B6="FT",C6>=1),12,IF(AND(B6="FT",C6<1),7,0)))

Signature

Greetings from New Zealand

>I am having trouble in my excel class again. Nothing seems to be even
>close
[quoted text clipped - 19 lines]
> thanks to whoever can help.
> Too old to be in college

Reply to this Message

Kimmer - 19 Apr 2008 02:08 GMT

Thank you very much from so far away. I keep thinking I am going to catch on
to this and then I hit a brick wall.
Thank you again.

Signature

Too old to be in college

> =IF(AND(B6="FT",C6>=5),17,IF(AND(B6="FT",C6>=1),12,IF(AND(B6="FT",C6<1),7,0)))
>
[quoted text clipped - 21 lines]
> > thanks to whoever can help.
> > Too old to be in college

Reply to this Message

T. Valko - 19 Apr 2008 04:40 GMT

In the context of this being an assignment and requiring the the use of
nested IF functions:

=IF(B6="FT",IF(C6>=5,17,IF(C6>=1,12,IF(AND(C6<1,C6>0),7,0))),0)

A more efficient way:

=IF(B6="FT",LOOKUP(C6,{0,0.00001,1,5},{0,7,12,17}),0)

Signature

Biff
Microsoft Excel MVP

>I am having trouble in my excel class again. Nothing seems to be even
>close
[quoted text clipped - 19 lines]
> thanks to whoever can help.
> Too old to be in college

Reply to this Message

T. Valko - 20 Apr 2008 02:55 GMT

Improvement

No real need for the AND function:

>=IF(B6="FT",IF(C6>=5,17,IF(C6>=1,12,IF(AND(C6<1,C6>0),7,0))),0)

=IF(B6="FT",IF(C6>=5,17,IF(C6>=1,12,IF(C6>0,7,0))),0)

Signature

Biff
Microsoft Excel MVP

> In the context of this being an assignment and requiring the the use of
> nested IF functions:
[quoted text clipped - 28 lines]
>> thanks to whoever can help.
>> Too old to be in college

Reply to this Message

Rate this thread: