calculating percentages based on checkboxes

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
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!


Photo of Courtney Rapp

Courtney Rapp

  • 242 Points 100 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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]) . . . .
(Edited)
Photo of Courtney Rapp

Courtney Rapp

  • 242 Points 100 badge 2x thumb
Yes! For all Field #B = " " I want them to be "is not blank", I thought that's what " " meant? 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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.
Photo of Courtney Rapp

Courtney Rapp

  • 242 Points 100 badge 2x thumb
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...
Photo of Forrest Parker

Forrest Parker

  • 598 Points 500 badge 2x thumb
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]
(Edited)
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,504 Points 5k badge 2x thumb
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.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
What is the field type for Field 1B? 
Photo of Courtney Rapp

Courtney Rapp

  • 242 Points 100 badge 2x thumb
Numeric. I also tried Nz, no luck. 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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. 
Photo of Courtney Rapp

Courtney Rapp

  • 242 Points 100 badge 2x thumb
I double checked, all Field #A or B are numeric lookup fields. Thanks for your help!
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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.