If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|