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

[Hector_Santilla]

I am getting the MIN and AVE of two fields from a table using the rollup function. When I use the expression IF to compare the rollup results, the IF statement delivers wrong information. Please, some one help

[Tim_Bourguignon]

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?

[Hector_Santilla]

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.

[Hector_Santilla]

image|356x499

[Bill_French]

Hector_Santillan:

even though the AvePrevWage and MinPrevWage fields have the same value.

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.

[Hector_Santilla]

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

[Bill_French]

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?

[W_Vann_Hall]

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?

[nea_lpatil]

@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

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

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

[Hector_Santilla]

{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