IF statement is not working properly when comparing rollup results from two fields

Hi @Hector_Santillan, happy to help. Can you please provide more information? What are you trying to do? What does your formula looks like? In what way is the result wrong? Maybe you can add a screenshot?

Yes, thank you for your reply.

I have a currency field. During a week, I want to know if that field has change. In order to do that I use the rollup function AVE and MIN. If the AVE and MIN are the same, then the currency field has not change, otherwise the field changed.
When I use the formula IF ((AvePrevWage = MinPrevWage), yes, no) the result is always no, even though the AvePrevWage and MinPrevWage fields have the same value.

image|356x499

I’ll bet they aren’t the same value. My hunch is the values (especially the averaged value) are far different from what you are rendering in that field. Consider how averages work - they are deeply fractionalized, so the likelihood they are slightly different, is great.

I recommend you test my hypothesis by simply increasing the decimals for the values in the table to see if they are really the same at deeper precisions.

Thanks, Bill.

I adjusted the decimals to the max permitted, but the results are the same. Any other options, or any idea on how to detect in the values changed during a week, instead of using the listed method? Thanks

Hmmm, I was almost certain that was the issue.

Um, have you tested your formula on two fields that are number types instead of rollup types just to be sure the formula is valid?

Unfortunately, here’s what I get when I duplicate your code:

Are you using a self-linked table, by any chance – that is, are {AvePrevWage} and {MinPrevWage}. rolled up from the same table in which you are performing the calculation, or are they rolled from links to another table?

@Hector_Santillan

I think following is happening.

@Bill.French is right. Both the numbers compared are not the same.

Decimal point number formatting limitation causes this problem for showing that two numbers are same.
Just multiply difference of two numbers by big number e.g. 10Billion as I did in my test below and it will display number which is not zero. It means that two numbers are different.

Please note that I converted text into number to preserve decimal points beyond default precision of 8.
I am using two numbers for comparison.
1.123456789
1.12345679

I need to look into data of @W_Vann_Hall to find out more about data used in the test because formula is the same but data might be different from Hector.

Neal

{AvePrevWage} and {MinPrevWage} are coming from different table, not from where the calculations are done.

The field {PrevWage} are formatted in currency values at the original table

Is any way I can find out if the field {PrevWage} varies during a period of time (eg. weekly?) instead of using the above method?

That’s how I have my table set up.

Try this for your formula:

IF(
    ROUND(
        AvePrevWage,
        2
        ) = ROUND(
            MinPrevWage,
            2
            ),
    'yes',
    'no'
    )

That will determine whether a floating point issue is causing the mismatch. That might happen if all of your rollups roll up more than a single record (in my test, the records where AVERAGE() = MIN() all have only a single linked record. (Note that changing the formatting of a field does not change the underlying data; ‘12.4’ and ‘12.40000000000001’ formatted as currency will both display as ‘$12.40’, but they won’t equal each other in a test.)

If that doesn’t work, you can either send (either publicly here or to me in PM) a read-only link to your base with copying permitted, and I’ll take a closer look.

Thanks Vann_Hall. Rounding the rollup fields is working. Thanks a lot.

Hector