+ Reply to Thread
Results 1 to 12 of 12

Averaging 2 drop down lists into a value

  1. #1
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Averaging 2 drop down lists into a value

    I read this thread which helped me very much. So I was able to link one drop down box to a list, which fills in the adjacent box. Now I need some help getting an average.

    Ex. Drop down 1 has a list of names which applies the associated numerical value in box 3. Drop down 2 has a list of the same names with the same associated values, and I need these 2 values averaged and put in box 3.

    The problem is that Drop down 2 doesn't always have a name. It may be left blank and so I need drop down 1 to continue applying to box 3 when drop down 2 is empty.

    This is what I have for drop down 1 to apply to box 3.
    HTML Code: 
    which I got from the thread I linked to at the top. Thanks for any help.
    Last edited by cawesjmu; 07-15-2009 at 02:37 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Averaging 2 drop down lists into a value

    Are you using another cell to look up the value of drop down two and return either the result or a blank? (As in your posted formula for drop down one)

    You would only need to test the results cell for drop down two and it is blank then look up and return the result for drop down one.
    Assuming D2 is the result cell for drop down 2.

    Here is the idea:
    =if(not(isblank(d2)),vlookup(D2),vlookup(C2))

  3. #3
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Averaging 2 drop down lists into a value

    If I understand your question correctly (sorry, I'm an excel n00b), then no, I'm using the same data set (located on Sheet 2) for both drop down 1 and drop down 2. I need a score for each name, so I have for example "John" associated with "5" and "Mary" associated with "7". If just John is present, then 5 works in the appropriate box and everything is great. But if Mary is also present, I need it to average the two scores and give me 6.

    After re-reading your question, perhaps this is the real answer. Yes, I'm using cell C2 for drop down one and D2 for drop down two. But like I said, both C2 and D2 are looking at Sheet2 at the same data list of names. I was thinking I might need to add a "AVERAGE" fx into the mix there somewhere, but I can't figure out where.

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Averaging 2 drop down lists into a value

    Perhaps you should post your workbook.

    Upon thinking about this, and may be my understanding isn't clear:
    if drop down 2 is empty and you want the value returned from drop down 1 to apply to the "Average", then this makes no sense, unless there are other cells also included in the formula. You indicate two cells are in view for this problem.

  5. #5
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Averaging 2 drop down lists into a value

    Yeah, I agree. Seeing it might be easier than explaining it. So to help with the visual again, I want to be able to have 2 names and get an average, but it be okay with just 1 name as well. If you select a name from the drop down box under "Name1" it will autofill the appropriate score, and I need to be able to add a selection from "Name2" and average the scores. Thanks for all the help so far.

    Edit: I realized my formula didn't drag down correctly. All the fields should work now in "averagescore(fixed)"
    Attached Files Attached Files
    Last edited by cawesjmu; 07-15-2009 at 01:17 PM.

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Averaging 2 drop down lists into a value

    In E2 and copied down.

    =if(and(isblank(C2),isblank(D2)),0,(IF(C2="","",VLOOKUP(C2,Sheet1!A2:B7,2,FALSE))+IF(NOT(ISBLANK(D2)),VLOOKUP(D2,A2:B7,2,FALSE),VLOOKUP(C2,A2:B7,2,FALSE)))/2)

  7. #7
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Averaging 2 drop down lists into a value

    Fantastic! You're my hero! Thank you very much. Oops, sorry, spoke too soon. Its's giving me an error (value not available) when I only have 1 name selected.
    Last edited by cawesjmu; 07-15-2009 at 01:31 PM.

  8. #8
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Averaging 2 drop down lists into a value

    Use this formula, it is more efficient and shouldn't give you errors.

    =if(and(isblank(C2),isblank(D2)),"",(SUMPRODUCT((A2:A7=C2)*(B2:B7))+SUMPRODUCT((A2:A7=D2)*(B2:B7)))/COUNTA(C2:D2))

  9. #9
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Averaging 2 drop down lists into a value

    Hmm, let me play around with it for a minute and see if I can get it to fit my actual worksheet. I gave you an example that had drop down boxes on the same sheet, where in my real file I have names and numbers on Sheet 2. I'll write back in a bit. Thanks again for the help

  10. #10
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Averaging 2 drop down lists into a value

    Alright! I think I got it this time. It behaves when there's only 1 name, and averages when there's 2. Thanks so much!

    Edit: Removed error code. Seems to work if I edit it properly. Any suggestion on how to drag the formula for the collum without changing the source column? A2:A7 changed to A3:A8, A4:A9, ect.
    Last edited by cawesjmu; 07-15-2009 at 02:02 PM.

  11. #11
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Averaging 2 drop down lists into a value

    You want to create named ranges for the list of names and the scores, then use this formula:

    =IF(AND(ISBLANK(A2),ISBLANK(B2)),"",(SUMPRODUCT((Names=A2)*(Scores))+SUMPRODUCT((Names=B2)*(Scores)))/COUNTA(A2:B2))

    Names =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
    Scores =OFFSET(Names,0,1)

    If your data is not going to grow then you don't need dynamic named ranges. If you dont want to use named ranges, then just use this version.

    =IF(AND(ISBLANK(A2),ISBLANK(B2)),"",(SUMPRODUCT((Sheet1!$A$2:A7=A2)*(Sheet1!$B$2:B7))+SUMPRODUCT((Sheet1!$A$2:A7=B2)*(Sheet1!$B$2:B7)))/COUNTA(A2:B2))

  12. #12
    Registered User
    Join Date
    07-15-2009
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Averaging 2 drop down lists into a value

    Beautiful. I believe I'm good to go with those $ signs. I'm learning. Thanks a ton.

+ 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