IF function Excel 2010

Can you guys help me out I trying to calculate the insurance PREMIUM base upon the following information.

Fulltime  employees pay $88 per period

Part-time employees are not eligible for insurance, so they pay nothing.  

Retired employees who are eligible for Medicare pay nothing but otherwise they pay $32 per period.

Thank you,

Name      BirthDate      Sex      Status      Medicare Eligible      Health Plan      Insurance Premium
Sally      5/15/1968          F          FT               N                           HMO      
Susan      4/16/1957         F          R               N                            PPO      
Mohad      2/5/1988        M          PT               N                             HMO      
Listera      6/30/1999       F          FT                N                             PPO      
Jill      12/17/1978          F             PT                N                             PPO      
Joe      3/18/1972          M             R                 Y                             HMO      
Sam      2/2/1948          M             R                 Y                             HMO      

TOTAL                                          $          -  

perla1962Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Formula is:

=IF(D10="FT",88,IF(D10="PT",0,IF(E10="Y",0,32)))

then copy down

See attached.

Dave
insurance2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dlmilleCommented:
While IF statements are very useful, and you should probably stay with the above recommendation...one can also code the formula as follows (and this is a sign that I'm bored in the middle of the night and really should be sleeping!:

=(D10="FT")*88+AND(D10="R",E10="N")*32 <- also a valid equation for this problem - assumes data starts on row 10 and copy down (replaces formulas in the spreadsheet I attached).

Where all the "positive hits" have multipliers so in this instance

(D10="FT")=TRUE will coerce to a 1 and thus multipled by 88 to get the result

Obviously, one can't be FULL TIME and RETIRED at the same time, so we can add the next formula to this one:

AND(D10="R",E10="N") <- retired and not on medicare = TRUE coerces to a 1 and thus multiplied by 32 to get the result.

Just another way to think about it when the conditions are exclusive - sometimes easier to write or interpret/debug than a nested IF - especially if you have a lot of conditions and they are exclusive ones.
0
perla1962Author Commented:
Exellent Thank you,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.