[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# IF function Excel 2010

Posted on 2011-10-29
Medium Priority
322 Views
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                                          \$          -

0
Question by:perla1962
• 2

LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 37049294
Formula is:

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

then copy down

See attached.

Dave
insurance2.xlsx
0

LVL 42

Expert Comment

ID: 37049369
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

Author Closing Comment

ID: 37049393
Exellent Thank you,
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦