Excel IF, AND, OR ?'s

[joeu2004]

I would appreciate any ideas or comments to assit me in locating the correct
formula.

In general, use the Evaluate Formula to step through the evaluation.
Tools -> Formula Auditing -> Evaluate Formula.

Status can be: FT or PT or CN;
IF FT =>1 they receive 5 days family leave.
IF FT <1 they rec 3 days.
IF PT >2 they rec 3 days.
all else 0.

my formula is:
=IF(OR(B6="FT",B6="PT"),IF(AND(C6>=5,C6<1,5,3),IF(C6>2,3,0)),"")

You have some misplaced right-parentheses. Unfortunately, the syntax
is legal; but the resulting logic does not make sense. Even if you
correct the syntax error (which might simply be typos in your
posting), I believe your attempt to simplify the logical terms is
faulty. Try the following:

=if(b6="FT", if(C6<1, 3, 5), if(and(b6="PT",C6>2), 3, 0))

 

[Randy Tingley]

Good evening.
I am having a problem with my IF, AND, OR formula. I believe is may be in
my formula itself.

Status can be: FT or PT or CN;

IF FT =>1 they receive 5 days family leave.
IF FT <1 they rec 3 days.
IF PT >2 they rec 3 days.

all else 0.

my formula is:

=IF(OR(B6="FT",B6="PT"),IF(AND(C6>=5,C6<1,5,3),IF(C6>2,3,0)),"")


Name Status Yrs Employed Vacation Leave Used Vacation Leave Remaining
Vacation Leave Family Leave Used Family Leave Remaning Family Leave
Abba FT 0.1 7 5 2 FALSE 0

--

I would appreciate any ideas or comments to assit me in locating the correct
formula.
This AND/OR has me baffled me for hours.
Thanks,
Randy

 

[Randy Tingley]

In general, use the Evaluate Formula to step through the evaluation.
Tools -> Formula Auditing -> Evaluate Formula.


You have some misplaced right-parentheses. Unfortunately, the syntax
is legal; but the resulting logic does not make sense. Even if you
correct the syntax error (which might simply be typos in your
posting), I believe your attempt to simplify the logical terms is
faulty. Try the following:

=if(b6="FT", if(C6<1, 3, 5), if(and(b6="PT",C6>2), 3, 0))

Joe,
Thank you very much! This works very well. I spent 3+ hours attempting the
formula.
Randy

 

[BoniM]

=IF(AND(B6="FT",C6>1),5,IF(OR(AND(B6="PT",C6>2),(AND(B6="FT",C6<1))),3,0))

 

[BoniM]

Sorry, left out an equal sign, should actually be:
=IF(AND(B2="FT",C2>=1),5,IF(OR(AND(B2="PT",C2>2),(AND(B2="FT",C2<1))),3,0))

 

[Kathrine]

Try this:

IF(OR(AND(B6="FT";C6>2);AND(B6="FT";C6<1));3;IF(AND(B6="FT";>=1);5;0))

Not testet though - and its in the middle of the night where I am (Norway)..

Good luck!

 

[Ron Coderre]

Hi, Randy

With
B6: (status....eg FT, PT, etc)
C6: (Yrs.....eg 0.5, 1, 5.5, etc)

Try something like this:
D6: =(B6="FT")*((C6>=1)*4+1)+(B6="PT")*((C6>2)*3)

Does that help?
***********
Regards,
Ron

XL2002, WinXP