Compare 2 columns and get count of rows nearest to 100

[sunitha_chellaiah]

Hi,

I want to compare 2 columns and get count of rows for which values are nearest to 100.

Example

In the above table, we need to compare Value1 and Value2 row by row and need to check how many rows for value1 is nearest to 100.

For eg, for the first row Value1 is nearest to 100 , so the count will be 1 . 

            for the second row Value2 is nearest to 100, so count will be 0. Similarly need to check for other rows.

In this case, Count of rows for value1 nearest to 100 will be 3.

Expected output will be:

ID	Date	Value 1	Value 2	Count
Total				3
V34A-J133S-MV-13-T	45444	90.0%	61.4%	1
V34A-J133S-MV-13-T	45474	149.6%	131.6%	0
V34A-J133S-MV-13-T	45505	8.3%	6.0%	1
V34A-J133S-MV-13-T	45536	149.1%	100.0%	0
V34A-J133S-MV-13-T	45566	67.8%	68.0%	0
V34A-J133S-MV-13-T	45597	79.2%	54.0%	1

Thanks in advance.

[Clement15 · Accepted solution]

Hello,

This formula works, you will have to adapt it to your measurements, but it should work

(fabs(sum([Value 1]) - 100) > fabs(sum([Value 2]) - 100))+1

Here I used 100, because in my test, the number was not in percentage

[sunitha_chellaiah]

Thanks for your quick reply.

I have tried your formula. But, I am getting count as 5 which is not correct.

Expected count is 3.

It should compare Value1 with Value2 and check how many rows of Value1 is nearest to 100.

[Clement15]

Hello,

I have the right result on my side. Can you share the application?

[sunitha_chellaiah]

It  worked.

After converting everything to percentage, it worked.

Thank you so much.