# Weighted Average for Surveys

**Lang Sui**Feb 8, 2017 8:35 AM

I have a large spreadsheet where I would like to calculate the weighted average of activities performed by individuals (TUCASEID). Below is a snap shot of the Tableau work sheet.

TUCASEID = individual

Tuactivity N = activity number

Tufinlwgt = weight of individual (TUCASEID)

Tuactdur = duration of activity in minutes

Tutier1Code = activity code ( ie. 18 = travel, 12 = household chores)

Scenario 1:

Average hours per day people spend doing an activity *for all people who participated in this activity*: sum(tuactdur*tufinlwgt)/sum(tufinlwgt*Ia)

Ia=1 if person spend time doing activity, and 0 if they did not

I want to calculated the weighted average of hours per day the individuals travel (Tutier1Code = 18). So in the image below (from what you can see), the equation would be [(20+30+20)*2022867.012324+(20*15+15)*11163308.9075+(2+10+1+6)*3196564.638743]/(2022867.012324+11163308.9075+3196564.638743)=227 min.

If I wanted to calculated the weighted average of hours per day people spend doing household chores (TutierCode = 2), the equation would be [(45+90+90)*2022867.012324+(120+85+15)*11163308.9075]/(2022867.012324+11163308.9075)=220.8 min. In this case TUCASEID 20050101050049 did not do any household chores and is not factored into the equation.

Scenario 2:

Average hours per day people spend doing an activity *for all people*: sum(tuactdur*tufinlwgt)/sum(tufinlwgt)

So in the second example: people who spent time doing household chores based on population, the equation would be [(45+90+90)*2022867.012324+(120+85+15)*11163308.9075]/(2022867.012324+11163308.9075+3196564.638743)=178 min. In this case TUCASEID 20050101050049 did not do any household chores but is still factored into the equation to account for entire population.

Overall I would like to visualize the data as bar charts, with colors that indicate different activities.