Help with analyzing Rank Data with Weighted Responses

Question

I'm working in Excel on data related to hotels. Responses need to be weighted based the percent of total rooms in the sample. (E.g. Hotel A has 12 rooms, sample is 100 rooms spread over x hotels. Hotel A's responses are weighted at 12%.)

Respondents were asked to rank their priorities 1-6, with 1 being their top priority.

I was able to figure out how to do a straight ranking (no weight) using excel.

The problem I'm having is weighting the responses. In other words, I want Hotel A's ranking to have an impact of 12% rather than 1/x.

Is this possible?

Answer 1

Laura, I’m sure it’s possible. I’m finding it hard to visualize your data, so I haven’t offered a concrete answer. Please tell us:

a) how many records (Excel rows) are there?

b) how many hotels are there?

c) how many respondents are there?

d) what are the columns of the records?

please add a comment, and I’ll be glad to respond.