Help with errors when doing calculations within IF Formula

Platform discussions
1

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:
Screenshot 2023-05-21 at 4.39.49 PM

The results look great:
Screenshot 2023-05-21 at 4.40.04 PM

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:
Screenshot 2023-05-21 at 4.43.35 PM
Screenshot 2023-05-21 at 4.43.50 PM

The full formula should actually be this:
Screenshot 2023-05-21 at 4.45.14 PM

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?

2

Here’s the easy fix for that:

IF ( 
    {Final ARV}="O",
    ROUND( 
        {Purchase Price} / {Estimated ARV} *100,
        0
    ),
    ROUND(
        {Purchase Price} /
        MAX(
            {Final ARV},
            1
        ) *100,
        0
    )
)

Essentially, you’re guaranteeing that it will always divide by a minimum of 1. I agree that the formula should be smart enough not to throw an error for the Else statement that isn’t reached, but at least the fix is simple!

Let me know if the formula has any issues. I haven’t tested it.

3

Thanks for the workaround, this solved it!