Avoiding a 0 result
Hi!
Context: We have tokens that are being spent at farmers markets. We call those "Incentive Redeemed." They originate from two different programs, funded by two different grants, and because they are identical when they are redeemed we do not know from where they originated. We call those "SNAP Incentive Issued" and "Prescription Incentive Issued." We apply math to divide the "Incentive Redeemed" between the two programs/grants, using percentages. Here's an example:
=IFERROR(([SNAP Incentive Issued]@row / ([SNAP Incentive Issued]@row + [Prescription Incentive Issued]@row)), 0)
The problem I am running into is when there are zero tokens issued ("SNAP Incentive Issued" = 0), but there are tokens redeemed. It doesn't happen often, but when it does, because of basic multiplication, the result ends up being 0.
It might not be possible, but does anyone know of a way to make the formula default to one column - either "SNAP Incentive Issued" or "Prescription Incentive Issued" - when the other is 0? It's so hard to explain, so please let me know if you need any clarification.
Thank you in advance for any help you're able to give!
Answers
-
I would put an IF(AND()) condition around your formula and check
The AND first checks your 2 conditions and if a match sets the result to blank otherwise use your formula.
If(AND([SNAP Incentive Issued]@row= 0, [Prescription Incentive Issued]@row) >0) , "", IFERROR(([SNAP Incentive Issued]@row / ([SNAP Incentive Issued]@row + [Prescription Incentive Issued]@row)), 0))
-
Thanks, @BrunoC ! I get what you're saying. Unfortunately, though, when I put that in it said "incorrect argument set."
-
Try this... sorry there were a few extra parenthesis
=If(AND([SNAP Incentive Issued]@row= 0, [Prescription Incentive Issued]@row >0) , "", IFERROR([SNAP Incentive Issued]@row / ([SNAP Incentive Issued]@row + [Prescription Incentive Issued]@row), 0))
-
Awesome, ok, we're making progress! I'm still running into an issue, though. It's possible for BOTH [SNAP Incentive Issued] and [Prescription Incentive Issued] to be zero. What then?
Help Article Resources
Categories
Check out the Formula Handbook template!