I have an input module where I need to enter value (%) and it should sum up to 100%

I have an input module where I need to enter value (%) and it should sum up to 100% and once it reaches 100% we should not be able to enter any more value. The values will be random and not equally distributed like in Breakback. Please suggest.

Answers

  • I would suggest using conditional formatting to show different colors when total < 100%, total = 100%, and total > 100%. If you use DCA and not allow users strictly not enter anything once total = 100%, then user won't have an option to update the values later. Up to you, conditional formatting or DCA>

  • Dynamic Cell Access is the best option to restrict the cell data entry. So in your scenario once if total becomes 100% restrict the data entry by DCA and to highlight it use conditional format as mentioned above.

  • Hello ,

    You can use DCA to restrict the write for the line item and base it on boolean that if value=100% then write is false else true

  • Anurag0911
    edited July 2

    Hi @Soumyajit
    Please use the following link: https://help.anaplan.com/dynamic-cell-access-55ae93e6-5139-4bbf-93f9-c8cb06f68f75
    You can use DCA (Dynamic cell access) to achieve this.
    If you want it to be exact 100% try doing an error message approach just beside the cell telling a person to enter the right values.
    But this should help you achieve what you want.
    Hope this helps.

  • Thanks for your response @Anurag0911 and @Kanishq17 , however in DCA if we reach 100% it will disable the cell to enter any more value. Since I am adding random value I may have to change the input for any particular product. It wont allow me to edit that cell anymore after reaching 100%.

  • Yes, and this is what @Dikshant was mentioning in the first comment.

  • The limitation of DCA is that once the % reaches 100%, as @Dikshant mentioned, is that the users won't have the ability to update values after the condition is met. For this reason, I would suggest using conditional formatting. For example:
    Line 1: 20%

    Line 2: 80%

    Line 3: 15%

    Line 4 (sum): 115%

    You could use CF to mark Line 4 bright red (or whatever color) if the value is over 100%.

    As an additional measure, you could also add a text line titled something like:
    Over 100%?

    With the formula being something like:

    IF Line 1 + Line 2 + Line 3 > 100%

    THEN "Yes, " & TEXT (Line 4 - 100%) & " over"

    ELSE "No"

    So that the difference is a lot more obvious. In the above case, the line would read, "Yes, 15% Over" (though you might have to play around with how it shows… I wrote that formula suggestion based on memory 😄).

    Not necessary but could be helpful. @Soumyajit

  • Make a line item that keeps it at 100% by subtracting the extra percentage off (115 - 100 = 15 [15.1 to keep it at 99.9%]← subtract this from the values or last value entered to keep the DCA off and use the message to give an idea what can be entered ) and might need to think how you want to subtract it from the input lines but would it it helpful to add this formulaic line to control the inputs? Just a thought might need more information on what the limitations are here. Hope this helps! @Soumyajit