+ Reply to Thread
Results 1 to 12 of 12

using an array, if, and count if command

  1. #1
    Registered User
    Join Date
    08-23-2007
    Posts
    9

    using an array, if, and count if command

    i have this worksheet to fill out, i will attach it if anyone wants to take a look at the whole thing.. but, basically, i need a command where it will look at gender which is in column c, then age which is in column d, then gpa to count it, which is in column e, the gpa its looking for is >=3.0... and that is only one criteria.. an example is g=m, a=15 or above, gpa = >=3.0.. i also think i messed up on another part.. if someone can kindly help me... Stdev
    Average
    Median
    Max
    Min
    Mode
    Count
    Avedev

    i also need to know how to format these.. thx for the help

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Buisness Man,
    Welcome to the forum.

    Have you looked at the Database functions DCOUNTA, DAVERAGE, etc. ?
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    08-23-2007
    Posts
    9
    Quote Originally Posted by mikerickson
    Buisness Man,
    Welcome to the forum.

    Have you looked at the Database functions DCOUNTA, DAVERAGE, etc. ?
    i think i can manage those functions, but what about setting up the format for that big function that i'm talking about? Thanks for the welcome :P

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    I don't understand what big function you are talking about.

    What result(s) do you want from what inputs?

    If you could give an example, that would help me understand.

  5. #5
    Registered User
    Join Date
    08-23-2007
    Posts
    9
    Quote Originally Posted by mikerickson
    I don't understand what big function you are talking about.

    What result(s) do you want from what inputs?

    If you could give an example, that would help me understand.
    i can pm u the worksheet, its pretty self explanatory from there, i have done a portion of it with errors, if u could help fix where i have messed up..

  6. #6
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    It may be that sumproduct is what you need but, without more detail it's impossible to say.

    http://xldynamic.com/source/xld.SUMPRODUCT.html

    Post your spread sheet (use a zipped file) and someone can look at it for you.

    Ed

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by BusinessMan
    i have this worksheet to fill out, i will attach it if anyone wants to take a look at the whole thing.. but, basically, i need a command where it will look at gender which is in column c, then age which is in column d, then gpa to count it, which is in column e, the gpa its looking for is >=3.0... and that is only one criteria.. an example is g=m, a=15 or above, gpa = >=3.0.. i also think i messed up on another part.. if someone can kindly help me... Stdev
    Average
    Median
    Max
    Min
    Mode
    Count
    Avedev

    i also need to know how to format these.. thx for the help
    For a count

    =SUMPRODUCT((C1:C100="m")*(D1:D100=15)*(E1:E100>3))

    For average gpa

    =AVERAGE(IF(C1:C100="m",IF(D1:D100=15,IF(E1:E100>3,E1:E100))))

    This 2nd formula is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like {and } appear around the formula in the formula bar.

    Stdev, Avedev, Median, Max, Min and Mode can all be done exactly the same way as Average, just replace "Average" with the relevant function.

    Note: for all formulas you can't use whole columns (unless you have Excel 2007), adjust ranges as necessary but all should be the same size

  8. #8
    Registered User
    Join Date
    08-23-2007
    Posts
    9
    anyone who is willing to take a look at the sheet, can you pm me your email, and i will gladly send it to youh

  9. #9
    Registered User
    Join Date
    08-23-2007
    Posts
    9
    bump please need this, its due tommorrow

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572
    Please zip your file and post it. In order to test formulas, we need data to test them on, and its very hard (and tedious) to come up with random names, sexes, gpa's, etc.
    Ben Van Johnson

  11. #11
    Registered User
    Join Date
    08-23-2007
    Posts
    9
    i have figured out all the functions, besides average if male, average if female

  12. #12
    Registered User
    Join Date
    12-08-2008
    Location
    NYC
    MS-Off Ver
    2003 & XP
    Posts
    43
    =average(if(c1:c200="m")*(d1:d200=15)*(e1:e200>=3),e1:e200))

    please remember to hit cntrl Alt enter to "Enter" the formula as it is an array and will not result in a correct answer unless you do that. However once you see the {} around your formula it is working and can copy the cells w/o having to Cntrl Alt Enter

    All of the below functions can be substitued for "Average" above

    Average
    Median
    Max
    Min
    Mode
    Count
    Avedev

    Enjoy

+ 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