Your problem can be easly solve I guess using the INDEX function. I think you don't need all the tests you've added. Instead you should simply:
- create a calculated field "Rank" having Index() as formula.
- sort by "Speaker Satisfaction Average".
If you have 22 different speakers (hence 22 rows in your table), the calculated field Rank will range from 1 (first row) to 22 (last row).
If you sort by speaker names, A will be ranked 1 and V will ranked 22.
If you sort ascending by Speaker Satisfaction, A wil be 1 and V 22 but N will be n° 2.
If your sort descending by Satisfaction, n°1 wil be V and n° 2 will be S.
Now if you'd like something more permanent, my guess is that you'll have to use some sql queries.
Hope this helped,
Speaker Rank support.twb.zip 17.4 KB
Thanks for the advice. What I'm looking for is something that is more permanent like you say. This data will be updated every month with new satisfaction scores, meaning that rankings will fluctuate frequently. Therefore I would really want something that will update itself, without me having to manually re-rank every month and re-upload the workbook.
What SQL queries did you have in mind?
The solution I proposed will actualize. You won't need to re-rank every month once you set up the sort of satisfaction field. Just actualize your datasource.
You can easily simulate it by entering new data in your excel file, update your data source and check the result.
Concerning the sql statement, it was a quick thought. Now that I think a bit more about it, I don't think you'll need any.
Thanks for the info
Can you define what you mean by "actualize your datasource"?
Sorry if I wasn't clear. I meant "refresh the data source".
1 of 1 people found this helpful
Thanks for your suggestions. With a bit of playing around with the Table Calculation I have achieved my desired result. Here is my method:
1. Create calculation called Speaker Ranking:
2. Use Advanced table calculation:
3. I then used a Size() calculation in the table as well to give me the total number of Speakers from a string field.
Thanks again - hope this helps someone else as well!