+ Reply to Thread
Results 1 to 8 of 8

Multiple variables:bunch of variables

  1. 10-14-2009, 04:54 PM #1
    sbq80 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Multiple variables:bunch of variables

    Hey all, first post so fingers are crossed.

    Ok I have made the formula below and it works fine.

    =IF(AC4>26,"EXCELLENT",IF(AD4>22,"ABOVE AVERAGE",IF(AD4>17,"AVERAGE",IF(AD4>13,"BELOW AVERAGE",IF(AD4<12,"POOR")))))

    My problem is I have a bunch of variables that need to be in there and apparently I am not smart enough to figure it out.

    For ex. I need four age groups where all the numbers will be diffrent for males and four for females.

    In this case, gender would be in column C (and would use 1 for male and 2 for female) age will be in column E.

    The above formula would be for age 20-29 but is not sorted by gender.

    Hope all that makes sense?????

    Thanks in advance
    Reply With QuoteRegister To Reply
  2. 10-14-2009, 05:18 PM #2
    rwgrietveld is offline
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: Multiple variables:bunch of variables

    To be able to help you we need a full data sheet of all catagories.

    Gender(m), Age(20-29), Score(0-12) => Poor
    ...
    Gender(m), Age(20-29), Score(22-100) => Excellent
    Gender(m), Age(30-39), Score(0-12) => Poor
    Gender(m), Age(40-49), Score(22-100) => Excellent

    Better would be to have a relation between the 3 variables.

    Gender(2)*Age(6 categories?)*Score(4) = 48 categories.
    Via MATCH(), VLOOKUP() this is easy to do.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com     (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com     (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo
    Reply With QuoteRegister To Reply
  3. 10-14-2009, 06:03 PM #3
    sbq80 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    re: Multiple variables:bunch of variables

    Hows this?
    I have several other catagories but will just copy and past and change numbers in the other cells.

    Again thanks.


    Male 20-29
    Excellent >80
    Above Average 79-61
    Average 60-46
    Below Average 45-35
    Poor <35

    Male 30-39
    Excellent >74
    Above Average 73-54
    Average 53-40
    Below Average 39-24
    Poor <24

    Male 40-49
    Excellent >61
    Above Average 60-46
    Average 45-34
    Below Average 33-19
    Poor <19

    Male 50-59
    Excellent >54
    Above Average 53-42
    Average 41-28
    Below Average 27-16
    Poor <16


    Female 20-29
    Excellent >80
    Above Average 79-55
    Average 54-32
    Below Average 31-23
    Poor <23

    Female 30-39
    Excellent >70
    Above Average 69-50
    Average 49-35
    Below Average 34-21
    Poor <21

    Female 40-49
    Excellent >54
    Above Average 53-36
    Average 35-23
    Below Average 22-15
    Poor <15

    Female 50-59
    Excellent >41
    Above Average 40-29
    Average 28-19
    Below Average 18-11
    Poor <11
    Reply With QuoteRegister To Reply
  4. 10-14-2009, 07:10 PM #4
    timmtamm is offline
    Forum Contributor
    Join Date
    02-11-2009
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 365
    Posts
    100

    re: Multiple variables:bunch of variables

    Your question is not very clear, but here's an answer to what I think you are asking.

    I know this is an inferior answer (I'm not very experienced at this), but it does work. A cell equasion has a limit to the conditions it can hold (somewhere around 20). So I just made different ones, each into a separate cell, and shoved it off screen so you wouldn't see it.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-14-2009, 07:51 PM #5
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    re: Multiple variables:bunch of variables

    You could do it like this:
    Please Login or Register  to view this content.
    The formula in C2 is

    =INDEX($F$1:$J$1, MATCH(B2, INDEX($F$2:$J$9, MATCH(A2, $E$1:$E$9, 0), 0) ) )
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 10-14-2009, 09:07 PM #6
    sbq80 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    re: Multiple variables:bunch of variables

    Sorry if I was not clear.
    Basically what I need to be able to do is when I enter a persons; gender, age and results, I need to have a "score" show up in cell.
    Reply With QuoteRegister To Reply
  7. 10-15-2009, 03:16 AM #7
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    re: Multiple variables:bunch of variables

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  8. 10-19-2009, 04:22 PM #8
    sbq80 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    re: Multiple variables:bunch of variables

    timm & shg, thanks for suggestions but those wont work for me.

    Anyone else??????????

    again gender would be in cell C1 (and would use 1 for male and 2 for female) age will be in cell E1.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1