Urgent Help! Percentage formula based off total number.

Options

I have been wracking my brain trying to get a quarterly percentage number based on the following:

First number is the quarterly total, the second is the percentage it needs to have automated this is for approximately 40 people and I cannot manually process these. Please help. I will most likely need a phone call to accomplish this task as I work much better when I can walk through something and do it.


66+ would also have the total of 25%

Best Answer

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Aimee Peyton-Greene ,

    I think I understand what you need. You have 40 people who produce a given number of something completed each quarter. You have a non-linear score from 0 to 25 (expressed as a %) that each person earns based on the number of something completed. Because there isn't a standard mathematical relationship between the number of something produced and the score you need to use a table to look up the score.

    To do this you'd use a VLOOKUP() function. To help you I'd need to see where your quarterly employee totals are coming from. Are you already able to calculate those? Are you reporting quarterly scores in a separate grid? You need to set up a VLOOKUP formula that finds the person's completed number in the table and returns the score.

    Send some screenshots and we'll try to help.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Aimee Peyton-Greene
    Options

    I have them in the same document right next to that column automatically calculating from the monthly totals. I am attaching a picture of the two columns. First will be a clearer view of the number and percent to show and the second is the two columns. I feel so lost! This wasn't really covered in the basics and I haven't had a chance to deeper dive into the formulas lessons.


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 12/04/20
    Options

    You can do a vlookup in the same sheet. Smartsheet sets up percentages as decimals in the 10th place. .25 is 25% - .5 = 50% .75 = 75% if you adjust both columns to be formatted as percentages you can display the correct percentage that way.

    It would be helpful to you if you created two additional columns for your lookup table. You can hide them once the formula is set up. But you would need to have unique column headers to reference the table. I would also remove the 0-47 number in the list. I added an IFERROR to the vlookup formula so if doesn't find an exact match, it will return 0. You can hide the new columns once you have your formula working.

    =IFERROR(Vlookup([2020 Q2 Total]@row, [Name of header for number column]:[Name of header for percentage column], 2, false),0)

    For more on Vlookup see:


  • Aimee Peyton-Greene
    Aimee Peyton-Greene ✭✭
    edited 12/04/20
    Options

    =It worked! Now onto more automation of the sheet.... EEEK

  • Aimee Peyton-Greene
    Options

    Well... you may not know what you started. haha! I will post my next one. You're wonderful. Thank you.

  • Aimee Peyton-Greene
    Aimee Peyton-Greene ✭✭
    edited 12/05/20
    Options

    Is it possible to have the formula leave the space blank if the answer is under 48? My boss does not want 0 to show because it can be demoralizing.



    Edited to add: Nevermind I figured it out. I just had to add those numbers to the table and leave the % column blank!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    You could also replace the zero with ""

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!