Urgent Help! Percentage formula based off total number.

Aimee Peyton-Greene

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%

Mike Wilday

hehe. Good luck. Please accept my answer. And don't hesitate to reach out in the community if you need more assistance. 😁

Mark Cronk

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

Aimee Peyton-Greene

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

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:

https://help.smartsheet.com/function/vlookup

Aimee Peyton-Greene

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

Mike Wilday

hehe. Good luck. Please accept my answer. And don't hesitate to reach out in the community if you need more assistance. 😁

Aimee Peyton-Greene

https://community.smartsheet.com/discussion/comment/270753#Comment_270753

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

Aimee Peyton-Greene

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

You could also replace the zero with ""