IF function Excel 2010

Posted on 2011-10-29
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
    LVL 41

    Accepted Solution

    Formula is:


    then copy down

    See attached.

    LVL 41

    Expert Comment

    While IF statements are very useful, and you should probably stay with the above 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

    Exellent Thank you,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now