# Thread: Query Expression for Weighted Average (Access 2000)

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## 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.

#### Posting Permissions

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