+ Reply to Thread
Results 1 to 7 of 7

setting up criteria for database functions

  1. #1
    Registered User
    Join Date
    01-17-2008
    Posts
    10

    setting up criteria for database functions

    New guy here:

    There's probably something really simple I'm missing, but I'm at a loss on this.

    I have four columns of data I'm working with: Carrier Name, Product Name, Number of Lives, and # of Days Before Completed (I work in insurance). I've been able to average the "# of days" column by carrier and product use DAVERAGE, but I'm also trying to do it by Number of Lives.

    I need to set up my criteria so that instead of looking for a specific value, it is averaging only the "# of Days" that correspond to a range of # of lives (2 to 14, 15 to 25, etc.).

    I'm horrible at explaining this, so let me know if I'm making no sense and I can upload the sheet I'm working on.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by RichardL
    ... I can upload the sheet I'm working on.
    That would be a good idea
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-17-2008
    Posts
    10
    No problem. I had to clean it up so the compliance people didn't hang me up by my toes for releasing too much information.

    Basically, I've got three worksheets: "Mat" contains a list of carriers and basic info about the individual quotes. "TotalAvgs" is the sheet that normally contains averages for "Mat." "Criteria" is usally hidden and contains the criteria, obviously, for my database functions.

    Like I said, I had to remove some information, so I hope it still will illustrate what I'm looking to do. Thanks.

    edit: That's what I get for doing too many things at once. What I want to do is instead of just averaging based on carrier and line of coverage (health, dental, disability, etc.) I'd like to have separate average sheets based on # of lives, as in 2-14, 15-24, etc. So I have no idea how to add this to the criteria.
    Last edited by RichardL; 01-20-2008 at 02:36 AM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Sorry you're going to have to re-explain what the problem is... I can't figure out what you are trying to do...

    be specific as to cell references and results you want to see and where.

  5. #5
    Registered User
    Join Date
    01-17-2008
    Posts
    10
    Sorry, like I said I'm bad at this.

    On the TotalAvgs sheet, it currently averages the "# of Days" column in the Mat sheet (column M), with Carrier (Column E) and Product Name (F) on the Mat sheet as criteria.

    If you look on the "Criteria" sheet, you can see where I've set it up with each Carrier and Product Name combination. I need to add another column of criteria which addresses number of lives based on the following tiers:

    (1) 2-14
    (2) 15-25
    (3) 26-50
    (4) 51-99
    (5) 100 and over

    I need to further average the "# of Days" on the TotalAvgs sheet, broken out by "# of Lives" (Column H on the Mat sheet). So that there is another sheet of total averages broken out by carrier and product name, but only for rows on the Mat sheet which contain between 2 and 14 lives (column H on the Mat sheet). I need additional averages sheets for each of the above-mentioned tiers.

    So basically I need to know how to add a range of numbers as opposed to a specific number to the criteria.

    Am I doing any better?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    I am not sure that you really need the Criteria sheet at all....it looks like a lot to maintain.....besides I am not sure how complex it would be to set up those extra criteria for each and every combination you have on that sheet.

    see attached.... in the Total Avgs Sheet, I created an equivalent formula in column F... that does not use the Criteria page at all....it looks at column B for 1 criteria and for each type (i.e. Health, Dental) you create that criteria too... or if you change the title in C2 to "Health" instead of "Medical", you can reference that cell.... the only other thing would be that the carrier names would have to match those in column E of your Mat sheet...(e.g. UnitedHealthCare should be UHC).

    Then in a similar formula in column G, I added the criteria of >=2 and <=14 for the number of Lives....

    These formulas are array formulas and must be confirmed with CTRL+SHIFT+ENTER... the first part of the formulas is the error check....

    Does that help...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-17-2008
    Posts
    10
    Wow, you totally did it. I'd been playing with an array formula all day (based on advice from another thread on this site), and still couldn't get it to work.

    I'm completely self-taught on Excel, so I know I'm using the whole criteria thing for database functions incorrectly, so this helps a lot.

    Thanks a lot!

+ 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