none
Calculating Ranks of Data RRS feed

  • Question

  • I have a spreadsheet that has three tas:

    First tab is raw data imported from a website. its about 10 columns and up to 300 rows. it has a mix of numbers in decimal form and percent. it all come over as text though

    Second tab is for ranking the values in the first tab. it stores the min and max values for the rankings 3 to -3.  each column from the raw data tab has its own ranking ranges.

    the third tab is a mirror of the raw data tab, but instead of number values, it calculates the rank of the value in this tab.

    I'm trying to figure out the best way to  do the calculations. it is a lot of data and the way I am currently doing it bogs down the system tremendously.

    I call a function that has two parameters - column and value.  the function calculates the ranking ranges for that column and stores it in local variables in the function, then a large nested if statement  compares the value to the range variables and returns the ranking to that cell. it runs for each cell in 300 rows, 10 columns.  also, I have to convert the percent values from text to number values in the second parameter of each function call, so it is a lot of calculating.

    I there an easier, more efficient way to get this ranking? Would nested if statements in the cell formula field run smoother? or should I add more tabs to get the data into a more user friendly format before calling my function?

    Tuesday, August 7, 2018 4:45 PM

Answers

  • I did the formulas in the cells instead of a function. It works better now. thank you for your help
    • Marked as answer by JHarding08 Friday, August 17, 2018 9:24 PM
    Friday, August 17, 2018 9:24 PM

All replies

  • Hi,

    Could you provide a sample file about your problem, I don't understand your requirement.

    Please send the file sample to our email address:

    ibsofc@microsoft.com

    Note: Please add the URL of the case in the email subject or body, please make sure that you have hidden your private information.

    I'm glad to help you.

    Regards,

    Emi

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, August 8, 2018 5:39 AM
    Moderator
  • Hi,

    Any updates about this problem now?


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, August 10, 2018 9:44 AM
    Moderator
  • Hi,

    Do this problem resolve now?

    Do you need more help about this problem now?

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, August 14, 2018 7:52 AM
    Moderator
  • I did the formulas in the cells instead of a function. It works better now. thank you for your help
    • Marked as answer by JHarding08 Friday, August 17, 2018 9:24 PM
    Friday, August 17, 2018 9:24 PM