Forum Discussion

CourtneyRapp's avatar
CourtneyRapp
Qrew Cadet
6 years ago

calculating percentages based on checkboxes

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!


Formulas and functions

11 Replies

Sort By
  • EvanMartinez's avatar
    EvanMartinez
    Qrew Elite
    6 years ago
    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]) . . . .
  • CourtneyRapp's avatar
    CourtneyRapp
    Qrew Cadet
    6 years ago
    Yes! For all Field #B = " " I want them to be "is not blank", I thought that's what " " meant? 
  • EvanMartinez's avatar
    EvanMartinez
    Qrew Elite
    6 years ago
    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.
  • CourtneyRapp's avatar
    CourtneyRapp
    Qrew Cadet
    6 years ago
    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...
    • ForrestParker's avatar
      ForrestParker
      Qrew Cadet
      6 years ago
      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]
      ) 
    • EvanMartinez's avatar
      EvanMartinez
      Qrew Elite
      6 years ago
      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.
    • EvanMartinez's avatar
      EvanMartinez
      Qrew Elite
      6 years ago
      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. 
    • CourtneyRapp's avatar
      CourtneyRapp
      Qrew Cadet
      6 years ago
      I double checked, all Field #A or B are numeric lookup fields. Thanks for your help!