Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Expression for Weighted Average (Access 2000)

    I'm working with two fields in an Access query: "RankOrder" (where respondents to a questionnaire ranked six factors from 1 to 6) and "RankLMH" (where same respondents also ranked same six factors either low (L), medium (M), or high (H) importance. My boss wants a "weighted average," whereby we multiply the RankOrder number by a number equivalent for low (1), medium (0.6), or high (0.3).

    I have been trying to create an expression in the the Access query for the weighted average, but I cannot get the expected results. In Excel, the formula I tried works fine (see below); but when I try to recreate the formula in Access as an expression, the results are incorrect--the incorrect results reflect whole numbers equal to the number in the "RankOrder" field.

    Could someone please take a look at the Access expression below and let me know what I need to change in order for it work correctly. Many thanks...Mary
    P.S. In Excel, "B2" is same as "RankOrder" in Access.

    EXCEL
    =B2*(IF(C2="H",0.3)+IF(c2="M",0.6)+IF(C2="L",1))

    ACCESS
    =WeightedAvg: [RankOrder]*((IIf([RankLMH]="H",0.3) Or IIf([RankLMH]="M",0.6) Or IIf([RankLMH]="L",1)))

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Expression for Weighted Average (Access

    Try this

    =WeightedAvg: [RankOrder]*(IIf([RankLMH]="H",0.3 ,IIf([RankLMH]="M",0.6) ,1)))

    IIF is an "If then else" expression, so what this says is:

    If [RankLMH]="H", then use 3, otherwise , If [RankLMH] = "M", use 0.6, otherwise use 1

    The EXCEL IF works the same way, so you could use the same syntax is EXCEL also.

    Note that what you get is not actually a wieghted average, just a weighted score. You then need to average the weighted scores.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Expression for Weighted Average (Access 2000)

    Create a small lookup table:

    <table border=1><td>RankLMH</td><td>Weight</td><td align = center>L</td><td align=right>1</td><td align=center>M</td><td align=right>0.6</td><td align=center>H</td><td align=right>0.3</td></table>
    Add this lookup table to your query, and join RankLMH to RankLMH.
    You can now use [RankOrder]*[Weight]

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Expression for Weighted Average (Access

    Many thanks, John...I will try it, and I'm sure it will work. Appreciate your quick resonse!...Mary

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Expression for Weighted Average (Access 2000)

    Many thanks, Hans....As usual, another great solution! I will try both John's expression and your lookup table ideas. Appreciate your quick response!...Mary

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Expression for Weighted Average (Access

    I think that the Lookup table is the better solution to your problem.

    Putting the weights in a table makes it easy to adjust the weights in the future, without having to go looking around in formulas for the old weights.
    Regards
    John



Posting Permissions

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