I’m having an issue with calculating a ratio of Purchase / ARV. My board has 3 columns that all have $ values: Purchase Price, Estimated ARV, and Final ARV. The way our process works is that initially we enter an Estimate ARV $, and later on we enter a Final ARV $. This means that at first, Final ARV will be blank.
I want to set up a Formula column for Purchase / ARV ratio where if Final ARV is blank, the calculation is done with Estimate ARV, and if FINAL ARV is not blank, the calculation is done with FINAL ARV.
As a test, I entered this formula:
The results look great:
But here’s where the issue is - when I try to replace either of those text displays with a formula that calculates the ratio, I see an error. Is this a bug? Is there a limitation of formulas where I can’t do a calculation nested within an IF formula?
Here is an example - in this case, if Final ARV is not zero, it should show the calculated ratio as a %. If the Final ARV is zero, it should still display the text “Final ARV is zero”, but here is what happens:
The full formula should actually be this:
But I always see the red exclamation points whenever Final ARV is zero, and the error says “Can’t divide by 0” - which I am aware of, and is the whole reason I am using an IF formula. Any ideas on how to get around this?