+ Reply to Thread
Results 1 to 14 of 14

questions with if statements and nested if statements

  1. #1
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    questions with if statements and nested if statements

    I have a set of data and I am looking for some help with several ways I have to break the data down;

    Using =IF(), transform *** into a 1 if *** is F and a 0 if *** is M. Call this Sex1. *** is broken down in cells c2-c21

    Using nested =IF(), transform age to another variable that combines age into ten-year age groups, beginning with the youngest age among the patients listed. Assign 1 to the youngest group. Call this Age1. Age is broken down in cells b3-b21 and the youngest age is 21

    Using nested =IF(), transform age to another variable that combines age into ten-year age groups, beginning at birth. Assign 1 to the youngest age group. Call this Age2. age is broken down in cells b3-b21

    Using nested =IF(), transform the variable Insurance into a new variable called Insurance1—that is, 1 if the insurance type is Medicaid, 2 if the insurance type is BCBS, 3 if the insurance type is private, and 4 for self-pay. Insurance is broken down in cells h2-h21

    Using nested =IF(), transform the variable Prior into a variable called Prior1, where no prior visits is coded None, 1 to 2 prior visits is coded Low, 3 to 4 prior visits is coded Medium, and 5 or 6 prior visits is coded High. Prior is broken down in cells i2-i21


    thanks for the help!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,566

    Re: questions with if statements and nested if statements

    Does it need to be IFs?

    Most of the things you describe can probably be done with other functions.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: questions with if statements and nested if statements

    Unfortunately it has to be if functions so any help would be appreciated

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,566
    I wasn't suggesting anything other than Excel worksheet functions.

  5. #5
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: questions with if statements and nested if statements

    No i completely hear you but it has to if functions, thanks for your help!

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,566
    Can I ask why?

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,508

    Re: questions with if statements and nested if statements

    Can you upload a dummy file with designed results?

  8. #8
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: questions with if statements and nested if statements

    Here's a file with the data, and it has to if functions because that is how the questions are being asked, thanks for the help!
    Attached Files Attached Files

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,508

    Re: questions with if statements and nested if statements

    Try this:
    SEX1:
    =IF(C2="F",1,0)
    Age1:
    =IF(B2<=30,1,IF(B2<=40,2,IF(B2<=50,3,IF(B2<=60,4,IF(B2<=70,5,IF(B2<=80,6,IF(B2<=90,7,8)))))))
    Insurance1:
    =IF(H2="Meicaid",1,IF(H2="BCBS",2,IF(H2="Private",3,4)))
    Prior1:
    =IF(I2<=0,"None",IF(I2<=2,"Low",IF(I2<=4,"Medium","High")))
    For Age2 condition, I am not sure there is different between Age1 (beginning at age) and Age2 (beginning at birth). Could you please break it down?

  10. #10
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: questions with if statements and nested if statements

    for the age question i believe age 1 is for the starting age in the data set while for age 2 it starts at 0 and goes from 0 as the starting point

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,508

    Re: questions with if statements and nested if statements

    Quote Originally Posted by Pat Excel View Post
    for the age question i believe age 1 is for the starting age in the data set while for age 2 it starts at 0 and goes from 0 as the starting point
    So simply edit Age1 with 10,20,30,...points.

  12. #12
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: questions with if statements and nested if statements

    Thanks for all your help, really appreciate it

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,508

    Re: questions with if statements and nested if statements

    Anyway, before closing this thread, can you share us the reason why other functions are not accepted? Instead of IFs, we can use LOOKUP, which is much more power than nested IF (>7 IFs,...)?

  14. #14
    Registered User
    Join Date
    01-31-2013
    Location
    baltimore, maryland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: questions with if statements and nested if statements

    It was the way the question was asked, I'm sure there are easier ways to do it but thank you again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1