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


IF function Excel 2010

Posted on 2011-10-29
Medium Priority
Last Modified: 2012-05-12
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                                          $          -  

Question by:perla1962
  • 2
LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 37049294
Formula is:


then copy down

See attached.

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.

Author Closing Comment

ID: 37049393
Exellent Thank you,

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question