Top Custom Scorecard Formulas


Related article- How to Use Custom Scorecard Formulas

Details:

Custom formulas can be used for a variety of purposes. One example might be for a supervisor to track their direct reports. If each direct report has its own Sales measurable, such as Sales Tom and Sales Eileen, these 2 separate measurables can be summed for a 'Sales Total' measurable that may be owned by the supervisor. The first example below would be created and owned by the supervisor. Changes to the Sales Tom and Sales Eileen measurables would automatically adjust on the 'Sales Total' measurable.

Important! 

When creating a measurable that may have a similar or exact name to another one in a different Scorecard please make sure to add a distinctive touch like the user’s initials, the group it belongs to, or anything else that can help you identify the measurables easily in the formulas search bar.

Example: Sales (SM) or Sales (Europe)

Simple Custom Operators

Parentheses and combining

Reusing measurables 

Weekly offsets

Details:

This is helpful if you wanted to display a running total or average for a set amount of weeks. To access the offset option, on your ‘Items Sold’ measurable, inside the formula, click the small triangle to the right of the words ‘Items Sold’. The below formula would display a running average of the Items Sold measurable for the past 3 weeks.

Weekly offsets - Calculating monthly data based on weekly data (Video)

Steps:

  1. Create a new measurable by clicking in the + sign in the top right corner of the Scorecard tile or in the “edit meeting” section. 
  2. Fill in the name of the measurable, select your goal, click on the Formula checkbox on the right side of the screen. 
  3. Type in the measurable’s name in which you want to base your calculations off four times (4) and add a plus sign (+) between each of them. (Eg. Measurable1 + Measurable1 + Measurable1 + Measurable1) 
  4. Leave the first measurable as it is and apply an offset on three of them by clicking on the carrot/arrow icon next to the measurable’s name and type in -1, -2, and -3. (That’s how you go back in time for 1 week, 2 weeks, and 3 weeks)

Year over year ratio (Video)

Details:

Note that this is dividing the current week's value by the value from 52 weeks ago. See the above formula for details on offsetting the week.

Steps:

  1. Create a new measurable by clicking in the + sign in the top right corner of the Scorecard tile or in the “edit meeting” section. 
  2. Fill in the name of the measurable, select your goal, click on the Formula checkbox on the right side of the screen. 
  3. Type in the measurable’s name in which you want to base your calculations off two times (2) and add a division sign (/) between them. (Eg. Measurable1/Measurable1) 
  4. Leave the first measurable as it is and apply an offset on the second measurable by clicking on the carrot/arrow icon next to the measurable’s name and typing in -52. (That’s how you go back in time for 52 weeks or the equivalent to a year)
  5. If you want to create a percentage (%) out of it, all you need to do is to apply a parenthesis (one at the beginning of the formula and one in the end) and multiply that by 100. 

Rounding the result - RoundK (Video)

Details:

Sometimes a formula will have an excess number of digits when there is only a need to show just a few numbers after the decimal. Use the ROUNDK ( A ; B ) expression to shorten the results of a formula. The ‘A’ portion is the formula, the ‘B’ is the maximum number of spaces a number can display after the decimal point.

Steps:

  1. Create a new measurable by clicking in the + sign in the top right corner of the Scorecard tile or in the “edit meeting” section. 
  2. Fill in the name of the measurable, select your goal, click on the Formula checkbox on the right side of the screen. 
  3. Type in the measurables names on which you want to base your calculations off. (Eg. Measurable1/Measurable2 or Measurable1/0.2) 
  4. Type in “RoundK” at the beginning of the line. 
  5. Open parenthesis between “RoundK” and the mathematical operation.
  6. Type in a semicolon at the end of the mathematical operation/equation and the number of decimal places you want to round your results.
  7. Close parenthesis at the end of the formula. 

Make a Percentage (%) out of a Formula (Video

Details:

When you’re creating the measurable, you’ll see % in the units dropdown, this will only indicate the sign for the results based on your formula, no matter the result. This will not convert the number into a %, for you to be able to do that you can follow the steps below.

Steps: 

  1. Create a new measurable by clicking in the + sign in the top right corner of the Scorecard tile or in the “edit meeting” section. 
  2. Fill in the name of the measurable, select your goal, click on the Formula checkbox on the right side of the screen. 
  3. Type in the measurables names on which you want to base your calculations off. (Eg. Measurable1/Measurable2) 
  4. The result shown in the Scorecard might display 0.075 even if you selected % for the units while creating or editing the measurable, which only represents the sign at the beginning or the end of the results. 
  5. To convert the result into an actual percentage you’d like to open and close parenthesis to the mathematical equation and multiply by 100. 

Calculating a Manual Average (Video)

Details:

Please take into consideration that this is not a real average formula, this helps you get an average when all the cells are filled out with the data, since this is the manual way of calculating the average, if you’re working with four measurable those four measurables need to be filled out in order to work properly.

Steps: 

  1. Create a new measurable by clicking in the + sign in the top right corner of the Scorecard tile or in the “edit meeting” section. 
  2. Fill in the name of the measurable, select your goal, click on the Formula checkbox on the right side of the screen. 
  3. Type in the measurable’s name in which you want to base your calculations off and add a plus sign (+) between each of them. (Eg. Measurable1 + Measurable2 + Measurable3) 
  4. Once you added all the measurables you want to use for the average, you can add parenthesis at the beginning of the formula and at the end, and divide it by the number of measurables you added. (See screenshot below) 

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us