Forum Discussion

MeaganMcOlin's avatar
MeaganMcOlin
Qrew Cadet
22 days ago
Solved

How to Calculate Completion Percentage Using Multi-Select Boxes

I’m trying to create a formula that calculates the completion percentage based on a series of multi-select checkboxes.

For example, if there are four checkboxes and three of them are selected, I’d like the formula to return 75%. Is there a way to achieve this in Quickbase? Any help would be greatly appreciated!

Thank you in advance!

formulas
  • MarkShnier__You's avatar
    MarkShnier__You
    21 days ago

    Try this

    var number NumberChoices = 4;

    var text value = ToText([My Multi select field]);


    var number NumberSelected =
    Count(
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    Trim(Part($value,4,";"))<>"",
    Trim(Part($value,5,";"))<>"",
    Trim(Part($value,6,";"))<>"",
    Trim(Part($value,7,";"))<>"",
    Trim(Part($value,8,";"))<>"",
    Trim(Part($value,9,";"))<>"",
    Trim(Part($value,10,";"))<>"",
    Trim(Part($value,11,";"))<>"",
    Trim(Part($value,12,";"))<>"",
    Trim(Part($value,13,";"))<>"",
    Trim(Part($value,14,";"))<>"",
    Trim(Part($value,15,";"))<>"",
    Trim(Part($value,16,";"))<>"",
    Trim(Part($value,17,";"))<>"",
    Trim(Part($value,18,";"))<>"",
    Trim(Part($value,19,";"))<>"",
    Trim(Part($value,20,";"))<>"");

    $NumberSelected / $NumberChoices

    You should be able to use this formula by just adjusting the formula variables at the top so it knows the number of choices and also it knows which field to act on. 

5 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    22 days ago

    Do you have four separate checkbox fields?  I am unclear from your question what field type we are dealing with here. If you have four separate check boxes for example, you could calculate the percentage like this.  

    Sum([checkbox 1],[checkbox 2],[checkbox 3],[checkbox 4])/4

     

     

    • MeaganMcOlin's avatar
      MeaganMcOlin
      Qrew Cadet
      21 days ago

      Mark,

      Great question. I have a multiple-select field; you can select up to 4 choices. This is just one example; some multi-select fields allow you to select up to 20 choices, which is why we don't have them as individual checkbox fields. 

       

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        21 days ago

        So in the example of which is a multi select field with four choices, you're looking to add a column (field) which would contain the values, 100%, 50%, 25% 50%. Is that right?

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    21 days ago

    Try this

    var number NumberChoices = 4;

    var text value = ToText([My Multi select field]);


    var number NumberSelected =
    Count(
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    Trim(Part($value,4,";"))<>"",
    Trim(Part($value,5,";"))<>"",
    Trim(Part($value,6,";"))<>"",
    Trim(Part($value,7,";"))<>"",
    Trim(Part($value,8,";"))<>"",
    Trim(Part($value,9,";"))<>"",
    Trim(Part($value,10,";"))<>"",
    Trim(Part($value,11,";"))<>"",
    Trim(Part($value,12,";"))<>"",
    Trim(Part($value,13,";"))<>"",
    Trim(Part($value,14,";"))<>"",
    Trim(Part($value,15,";"))<>"",
    Trim(Part($value,16,";"))<>"",
    Trim(Part($value,17,";"))<>"",
    Trim(Part($value,18,";"))<>"",
    Trim(Part($value,19,";"))<>"",
    Trim(Part($value,20,";"))<>"");

    $NumberSelected / $NumberChoices

    You should be able to use this formula by just adjusting the formula variables at the top so it knows the number of choices and also it knows which field to act on. 

    • MeaganMcOlin's avatar
      MeaganMcOlin
      Qrew Cadet
      20 days ago

      Mark,

      You have once again saved the day!

      This worked. Thank you!
      - Meag