Weighted Averages in Access based only on certain values

[Yoni Hasid]

Hello,
Hope you can assist me. I am trying to calculate weighted avg in the field based only on certain values. The field contains response ratings received from the survey between 1-5. I am trying to calculate weighted % only for responses with ratings 4 and 5. Would you please direct me to what expression formula can be used for this?

[ADezii]

Create a Query restricting Response Ratings to 4 and 5 only, then apply Weighted Averages to the Result Set. To restrict Response Ratings:

SELECT Table1.*, Table1.[Response Ratings]
FROM Table1
WHERE (((Table1.[Response Ratings])=4 Or (Table1.[Response Ratings])=5));

[Yoni Hasid]

Thank you. I am relatively new to Access. In the query that I already have I have 5 fields where responses can be 4 and 5. So I need to be able to weight avg each of the field on 4 and 5 responses. Also, is there a way to write this as formula in expression builder since I needs this to be calculated on the report?

[ADezii]

Need greater detail along with some sample Data...

[Yoni Hasid]

Here is the extract from the query I use for the report. What I am trying to accomplish to get weighted averages for each of the category with numbers on the sheet and base weighted avg % only on responses with ratings 4 and 5. More so, I need to do also weighted averages for:

1.For each Field (with response ratings)
2.For overall of all responses across fields with 4 and 5 ratings for each Company Name field
3.For overall for all responses across all fields for all companies per SVP.

All based only on responses with 4 and 5 ratings.

Not sure if this is confusing....

Report DateAM SVP    DeptIDCompany NameTravelerNameDKNumberPNRNNumberField1Field2Field3Field4Field5
14-Oct-13    Name1    521745    XYS    Name1    3351000101    GGGG    5    5    5    5    5
14-Oct-13    Name2    521210    ABC    Name2    3462000001    VVVV    3    5    5    4    4
14-Oct-13    Name3    104455    XXC    Name3    6791000101    UUUU    5    5    5    5    5
14-Oct-13    Name4    161130    CGF    Name4    7490060506    UUUU    5    5    5    5    5
14-Oct-13    Name5    523530    BKH    Name5    3841000101    QQQQ    5    5    5    5    5
 
 

[zmbd]

Yoni Hasid:
Please be aware of the fact that attachments are not often downloaded by our experts/moderators for several reasons. In my case, my company's IT-Security policy prohibits doing so.

Instead, would you mind taking a small subset of your data in the Excel file and cut-n-paste. If you use the [CODE/] format button to place the [code] tags, the text will retain the intended spacing (with some minor tweaks (^_^) )

The other thing that will help will be to see the actual SQL in your query. If you will open the query in design mode, right-click in the table area, and select the SQL-View option. You will be taken to a view with the actual SQL behind the query. Please cut-n-paste this in a post, select the text and format it using the [CODE/] format button to place the [code] tags around the SQL text.

[Yoni Hasid]

Please let me know if the data sample makes sense.

[ADezii]

There are several Algorithms that compute Weighted Averages and quite honestly I am a little confused as to exactly what you are looking for. What I did was to compute the Average of Fields1...Field5 and return an Integer Value. The only requirements are that Fields1...Field5 cannot be NULL and that they are Integer Values.

1. Table1:
   

   SomeName    Field1    Field2    Field3    Field4    Field5
   ABC           13         12        40        20        15
   DEF          123        561       987       444       334
    

2. Function definition:
   

   Public Function fCalcAvg(lngF1 As Long, lngF2 As Long, lngF3 As Long, lngF4 As Long, lngF5 As Long) As Integer
     fCalcAvg = ((lngF1 + lngF2 + lngF3 + lngF4 + lngF5) / 5)
   End Function
    

3. SQL:
   

   SELECT Table1.SomeName, Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table1.Field5, 
   fCalcAvg([Field1],[Field2],[Field3],[Field4],[Field5]) AS Average
   FROM Table1;

4. Query Results:
   

   SomeName    Field1    Field2    Field3    Field4    Field5  Average
   ABC           13         12        40        20        15     20
   DEF          123        561       987       444       334    490