Hi there,
I am trying to figure out a formula to be placed in column "BVC Check" of the attached when the "BV" value for each unique "StationID" decreases for three consecutive "Extract Dates".
I tried to get the formula using co-pilot and although it came back with something I could not get it working successfully, I thought maybe a fellow human would have the answer.
Alan
Your file is full with links to your Sharepoint site and these will obviously not update for us. I've copied the table and pasted it back as values. Then I only kept the three relevant columns, deleted all rows with blank or #NA BV values. Then a simple pivot table and a helper function outside it will give you the alert you need (TRUE or FALSE)
However, I found no stations that had decreasing BV values for three consecutive days in a row. So, I just faked a few numbers to test the concept.
The attached file contains this, perhaps a bit clunky, solution. If you need to do this very frequently and on much larger data sets you may want to choose a more sturdy approach. That is, connect to the data with PowerQuery (transformed into what I did manually) and pivot the data. Load back to Excel add the check-formula in separate (calculated) column.
A single formula solution in the BVC Check column with the data shaped as you have it will be difficult if not impossible, I believe.
If your data doesn't have missing date/station combinations, and is sorted as in your sample, then I think something like this would work:
=IF($J2="","",LET(_data,TAKE(FILTER($T$2:$T2,$J$2:$J2=$J2),-3),IF(ROWS(_data)<3,"",SUM((TAKE(_data,-2)<TAKE(_data,2))+0)=2)))
It returns "" if there aren't three or more dates to check, FALSE if the last three dates are not all decreasing, and TRUE if they are. Only lightly tested though!
@riny Hi Riny, many thanks for taking the time to look at this in such detail. I should have thought to look at PowerQuery for this problem, I am going to do that now. Though Velouria's formula appears to be working. Thanks again.
@debaser Hi Velouria, many thanks for this - I added your formula to my main table and it appears to be working well! I'll test it out a little more thoroughly over the coming days, very helpful though, thank you.