Compare values on a certain date

Question

Hi all!

I have a database looking as follows

IDCountryDateValue1Germany01/01/2020102Germany02/01/2020153Germany02/01/202034Germany03/01/2020175France01/01/202025

I am trying to create a formula that will allow me to select the value for the same country and on the same date that is most possibly between the value for the same country on the day before and the day after the given date.

So in this example, I am looking for a formula that will help me deleting the value "3" for Germany on 02/01/2020, and keep the value "15" for Germany on 02/01/2020.

Please note that I have more than 100 countries in the database, and each date will appear for every country.

Could anyone help with some suggestions? So far I have been able to isolate the duplicate values (used this), but I can't figure out how to remove the incorrect one.

Thanks a lot & have a great day!

grossal · Accepted Answer

Hi @Andre2020,

I think I got something for you:

The Main Formula is:

IF not ([Row-1:Date] = [Date] AND [Row-1:Country] = [Country])
THEN "No"
ELSEIF ABS(([Row-2:Value] + [Row+1:Value]) / 2 - [Value]) < ABS(([Row-2:Value] + [Row+1:Value]) / 2 -[Row-1:Value])
THEN "Previous" 
ELSE "Yes" 
ENDIF

What I try to do: I add the one before the same and the one after it together, build the average from them and subtract the current value. The one that is lower will be the one that fits better in the row. I think you need to test it on a bigger sample and see if the logic applies on the bigger scale.

The seconds formula just replace the "previous" with "yes" to filter them. Note: This won't clear up the 'previous' in the Remove column, but you don't need to clean them up, you will most likely deselect the column.

Workflow attached. Let me know what you think.

Best

Alex