Skip to main content

Formula based on linked record and sum [solved]

  • December 7, 2018
  • 2 replies
  • 34 views
J
Forum|alt.badge.img

The idea being - how to track personal OKRs and see the progress.

I’ve created a version of the base concept but am having trouble with two things:

  1. Column “Progress (KR%)” doesn’t seem to be calculating correctly the percentage - it does 1% rather than 100%. I imagine there is some basic math I just skipped over but am curious what this is…
  • The formula I’m using is: {Progress (KR)}/{Key Result}
  1. What formula to use to calculate column “Progress (O%)”. In plain terms it would be "If column called ‘Objective’ is ‘Objective 1’, then add the numbers in column ‘Key Result’ and divide by ‘Progress (KR)’. And then the same for objective 2 and objective 3.

Help would be greatly appreciated and I’d be happy to push this out to the community as a resource if others would find it helpful (I haven’t come across this yet but if it’s already out there, I’d be super happy to just use that one instead).

Thanks so much!

    2 replies

    J
    Forum|alt.badge.img+18

    As far as #1 goes, just add *100 to the formula – Airtable’s “Percentage” fields don’t convert to actual percentages, they just stick a “%” onto the end of the result of the formula or input.

    ({Progress (KR)} / {Key Result}) * 100

    I don’t understand how your request #2 is any different than #1 – it looks to me like you are just requesting to do the same equation that #1 is expressing. You say

    but your Key Result column is a single number input field. There is no way for it to have more than one number. Do you mean that you want to SUM all of the Key Results that are linked to “Objective 1” together, and figure out their collective Progress (KR)? If so, that would need to be done in the “OKR’s” table where “Objective 1” exists. You’d use a Rollup field pointed at the “End of Year Plans” table and the “Key Results” field, and using SUM(values), and then the same for the “Progress (KR)” field, with SUM(values). Then, create a new Progress (KR% field in the OKR’s table that calculates the percentage for an entire Objective.

    J
    E
    J
    Forum|alt.badge.img
    • Julia_Feld
    • Author
    • Inspiring
    • December 7, 2018

    As far as #1 goes, just add *100 to the formula – Airtable’s “Percentage” fields don’t convert to actual percentages, they just stick a “%” onto the end of the result of the formula or input.

    ({Progress (KR)} / {Key Result}) * 100

    I don’t understand how your request #2 is any different than #1 – it looks to me like you are just requesting to do the same equation that #1 is expressing. You say

    but your Key Result column is a single number input field. There is no way for it to have more than one number. Do you mean that you want to SUM all of the Key Results that are linked to “Objective 1” together, and figure out their collective Progress (KR)? If so, that would need to be done in the “OKR’s” table where “Objective 1” exists. You’d use a Rollup field pointed at the “End of Year Plans” table and the “Key Results” field, and using SUM(values), and then the same for the “Progress (KR)” field, with SUM(values). Then, create a new Progress (KR% field in the OKR’s table that calculates the percentage for an entire Objective.


    Wow thank you so much! I was not expecting such a thorough assist here. That totally worked. Oddly, after maybe even years of using Airtable, I actually think this helped me understand how to use various field types and when tables can connect meaningfully during calculations.

    J
    Terms & ConditionsAccessibility statement