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!
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!
 272 Points
Posted 3 months ago
Evan Martinez, Community Manager
 9,790 Points
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]) . . . .
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)
 272 Points
Yes! For all Field #B = " " I want them to be "is not blank", I thought that's what " " meant?
Evan Martinez, Community Manager
 9,790 Points
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.
 272 Points
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...
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...
 752 Points
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:
Try the following:
If
(
[Checkbox]=true AND [Field 1B]<>0
,[Field 1A]/[Field 1B]
)
(Edited)
Evan Martinez, Community Manager
 9,790 Points
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.
Evan Martinez, Community Manager
 9,790 Points
What is the field type for Field 1B?
Evan Martinez, Community Manager
 9,790 Points
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.
 272 Points
I double checked, all Field #A or B are numeric lookup fields. Thanks for your help!
Evan Martinez, Community Manager
 9,790 Points
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.
Related Categories

Formulas & functions
 2792 Conversations
 64 Followers