Discussions

Expand all | Collapse all

calculating percentages based on checkboxes

  • 1.  calculating percentages based on checkboxes

    Posted 10-10-2018 18:57
    I am looking for a way to calculate a percentage based on a checkbox and if a value exists for a specific field. So basically:

    If(([Checkbox]=true) & [Field 1B]=" ", [Field 1A]/[Field 1B], 
    ([Checkbox]=true) & [Field 2B]=" ", [Field 2A]/[Field 2B],
    ([Checkbox]=true) & [Field 3B]=" ", [Field 3A]/[Field 3B]),...

    My goal is if a record's Checkbox is checked and it contains a value in Field 1A, then calculate Field 1A & Field 1B percentage. If Field one 1A does not contain a value, then check Field 2A for value, and Field 2B/Field 2A (and so on). 

    As of now, the result I'm getting is blank. I've tested with various Fields and not getting a different result. Would appreciate any help!




  • 2.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:00
    Hi Courtney,

    Looking at this formula the second condition in your if is [Field 1B]=" " which would be if this checkbox is true and Field 1B is blank then divide Field 1A by Field 1B which would be blank and wouldn't return a value then. Should it instead be when Field 1B is not equal to blank or is not null?

    Something like 

    If(([Checkbox]=true) & [Field 1B]<>0, [Field 1A]/[Field 1B], 
    ([Checkbox]=true) & [Field 2B]<>0, [Field 2A]/[Field 2B], 
    ([Checkbox]=true) & [Field 3B]<>0, [Field 3A]/[Field 3B]) . . . .


  • 3.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:03
    Yes! For all Field #B = " " I want them to be "is not blank", I thought that's what " " meant? 


  • 4.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:06
    In that instance if they are numeric fields I would suggest using <>0, which is the formula notation for is not equal to 0. Then it will check the numeric field is not zero and run the math. This will try to run the math if that number in those field is ever negative though as well. Where something like [Field B]>0 will only do the math if that field is a positive number, depending on how the math in that field works out.


  • 5.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:20
    So it is a numeric field, but the <>0 is not working? Here's how I entered:

    If(([Checkbox]=true) & [Field 1B]<>0, [Field 1A]/[Field 1B]

    I'm getting this error: 


    Is there a way to do not Null? I tried just not IsNull and that didn't work either...


  • 6.  RE: calculating percentages based on checkboxes

    Posted 10-15-2018 14:23
    I think that your issue is with the "&".  You use this to concatenate strings together.  To check two conditions you need to use "AND". 

    Try the following:

    If
    (
    [Checkbox]=true AND [Field 1B]<>0
    ,[Field 1A]/[Field 1B]


  • 7.  RE: calculating percentages based on checkboxes

    Posted 10-15-2018 14:32
    Forrest I think you hit the nail on the head. I glossed right over those ampersands when I was looking at the operators for the formula.


  • 8.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:25
    What is the field type for Field 1B? 


  • 9.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:30
    Numeric. I also tried Nz, no luck. 


  • 10.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 19:47
    The <> operation should work just fine with a Numeric type field I have a number of fields that use it in calculations in my live apps. If you are having an issue with that operator I would confirm the field types of the fields in use and make sure they are all Numeric or formula Numeric fields. If you are still having issues then I would suggest creating a case with the Care team so they can take a look at the formula inside of your application and see what might be causing you to get those error messages as it could be a type mismatch that is sometimes easier to identify from inside the application. 


  • 11.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 20:00
    I double checked, all Field #A or B are numeric lookup fields. Thanks for your help!


  • 12.  RE: calculating percentages based on checkboxes

    Posted 10-10-2018 20:06
    Sorry I couldn't help more, a Care rep should be able to get in your application and pick apart what might be causing that error to flag when building that formula and help you get across the finish line. Thanks for your time today Courtney.