Formula for rating results
 

Look for a help creating a formula, if it possible, to find out the overall
rating results
example - first row going across lists all the options provided - there are
20 options and people rated the options in order of preference
first column going down lists the poeples names
for each name - going across have entered the rating given for an option 1
thru 20.

Is there a way to find out the overall rating of an option.

What I want to do in the end is build a list of the options and list them in
the order of the preference, based on the ratings.
i.e. if the 3rd option was the first choice by everyone then would put it
first on the new list.

Thanks for any help
Nadine

Formula for rating results
 

At bottom of table (say row 101) calculate the sum for each option
=SUM(B2:B100)
and copy across to all the other options. (should go to column U)
In row 202
=RANK(B101,$B$101:$U$101,1)
and copy across

Now, to generate your list of options in order of preference (this assumes
that people marked a 1 for option they wanted most, 2 for 2nd favorite, etc.)

=INDEX($B$1:$D$1,1,MATCH(ROW(A1),$B$6:$D$6,0))

Copy this down as needed
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

Look for a help creating a formula, if it possible, to find out the overall
rating results
example - first row going across lists all the options provided - there are
20 options and people rated the options in order of preference
first column going down lists the poeples names
for each name - going across have entered the rating given for an option 1
thru 20.

Is there a way to find out the overall rating of an option.

What I want to do in the end is build a list of the options and list them in
the order of the preference, based on the ratings.
i.e. if the 3rd option was the first choice by everyone then would put it
first on the new list.

Thanks for any help
Nadine

Formula for rating results
 

Thanks,
I'll give it a try.

Why do the Sum part, wouldn't that be just counting up a total.

For the index part
Where would I be putting the index, is that just going to be another row
below the Rank row.
Also not understanding the index formula, why only going from B1-D1 and B6 -D6

"Luke M" wrote:

At bottom of table (say row 101) calculate the sum for each option
=SUM(B2:B100)
and copy across to all the other options. (should go to column U)
In row 202
=RANK(B101,$B$101:$U$101,1)
and copy across

Now, to generate your list of options in order of preference (this assumes
that people marked a 1 for option they wanted most, 2 for 2nd favorite, etc.)

=INDEX($B$1:$D$1,1,MATCH(ROW(A1),$B$6:$D$6,0))

Copy this down as needed
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

Look for a help creating a formula, if it possible, to find out the overall
rating results
example - first row going across lists all the options provided - there are
20 options and people rated the options in order of preference
first column going down lists the poeples names
for each name - going across have entered the rating given for an option 1
thru 20.

Is there a way to find out the overall rating of an option.

What I want to do in the end is build a list of the options and list them in
the order of the preference, based on the ratings.
i.e. if the 3rd option was the first choice by everyone then would put it
first on the new list.

Thanks for any help
Nadine

Formula for rating results
 

SUM is to get an idea of how high people rated the option. Could
theoretically use average as well, you just need a single value to evaluate.

My apologies on the INDEX function. The rank function should be in row 102,
not 202. I forgot to expand it to meet your scenario. Should be:

=INDEX($B$1:$U$1,1,MATCH(ROW(A1),$B$102:$U$102,0))

The first array should cover the names of your options. The 2nd array covers
the rank functions you just created previously. Again, my apologies for the
confusion.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

Thanks,
I'll give it a try.

Why do the Sum part, wouldn't that be just counting up a total.

For the index part
Where would I be putting the index, is that just going to be another row
below the Rank row.
Also not understanding the index formula, why only going from B1-D1 and B6 -D6

"Luke M" wrote:

At bottom of table (say row 101) calculate the sum for each option
=SUM(B2:B100)
and copy across to all the other options. (should go to column U)
In row 202
=RANK(B101,$B$101:$U$101,1)
and copy across

Now, to generate your list of options in order of preference (this assumes
that people marked a 1 for option they wanted most, 2 for 2nd favorite, etc.)

=INDEX($B$1:$D$1,1,MATCH(ROW(A1),$B$6:$D$6,0))

Copy this down as needed
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

Look for a help creating a formula, if it possible, to find out the overall
rating results
example - first row going across lists all the options provided - there are
20 options and people rated the options in order of preference
first column going down lists the poeples names
for each name - going across have entered the rating given for an option 1
thru 20.

Is there a way to find out the overall rating of an option.

What I want to do in the end is build a list of the options and list them in
the order of the preference, based on the ratings.
i.e. if the 3rd option was the first choice by everyone then would put it
first on the new list.

Thanks for any help
Nadine

Formula for rating results
 

Thaks, Index makes more sense now. And after using the Sum function,
understand how it works to get the ranking.

All worked for me, exactly what I needed
thank you very much.


"Luke M" wrote:

SUM is to get an idea of how high people rated the option. Could
theoretically use average as well, you just need a single value to evaluate.

My apologies on the INDEX function. The rank function should be in row 102,
not 202. I forgot to expand it to meet your scenario. Should be:

=INDEX($B$1:$U$1,1,MATCH(ROW(A1),$B$102:$U$102,0))

The first array should cover the names of your options. The 2nd array covers
the rank functions you just created previously. Again, my apologies for the
confusion.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

Thanks,
I'll give it a try.

Why do the Sum part, wouldn't that be just counting up a total.

For the index part
Where would I be putting the index, is that just going to be another row
below the Rank row.
Also not understanding the index formula, why only going from B1-D1 and B6 -D6

"Luke M" wrote:

At bottom of table (say row 101) calculate the sum for each option
=SUM(B2:B100)
and copy across to all the other options. (should go to column U)
In row 202
=RANK(B101,$B$101:$U$101,1)
and copy across

Now, to generate your list of options in order of preference (this assumes
that people marked a 1 for option they wanted most, 2 for 2nd favorite, etc.)

=INDEX($B$1:$D$1,1,MATCH(ROW(A1),$B$6:$D$6,0))

Copy this down as needed
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

Look for a help creating a formula, if it possible, to find out the overall
rating results
example - first row going across lists all the options provided - there are
20 options and people rated the options in order of preference
first column going down lists the poeples names
for each name - going across have entered the rating given for an option 1
thru 20.

Is there a way to find out the overall rating of an option.

What I want to do in the end is build a list of the options and list them in
the order of the preference, based on the ratings.
i.e. if the 3rd option was the first choice by everyone then would put it
first on the new list.

Thanks for any help
Nadine