2 Replies Latest reply on Nov 25, 2016 11:41 AM by Eric Hedekar

# Comparing rank of average across two demographics

I have a set of average weighted ratings for each singer.  This has been split into two sheets, one showing female respondents, one showing male respondents, and each sheet has then been ranked.  I'd love to create a difference score for each group but I have no idea how to get this going.  By difference score I mean if males on average rate Celine Dion a 4.3/10 and females rate her a 9.2/10 then her difference score should be +4.9 (or -4.9, either direction works).  Sadly I'm working with client data so I can't share a workbook.

I'd also love to get a difference rank.  As in if males rank Celine Dion the 30th best singer in the set (based on the weighted average score) and females rate her as the 2nd best then her difference rank would be +28 (or -28).

The weighted average is a calculated field.  The Male/Female sheets are built the same but with filters in place on the gender variable.

I tried building a calculation that used IF([Gender]="Male") THEN ([Weighted Average]) but because the weighted average is an aggregate function this didn't work.  Do I need to rebuild my average calculation with every variable wrapped in an IF([Gender]="Male") case logic?

• ###### 1. Re: Comparing rank of average across two demographics

Hey Eric,

The first part of your question is answered here:

As for calculating the difference between weighted averages, that seems a bit tougher. Nicholas Hara any ideas?

-Diego

• ###### 2. Re: Comparing rank of average across two demographics

Hi Diego, the link you shared for the first part no longer contains an answer.  The "correct answer" in that thread has a broken workbook and the link to the instructional video is dead.