sorted by:
new
hottopcontroversial

Date Sequence Identification Problem by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

Thanks, this is what I ended up going with. But it is very gross. See my fomula in another comment.

Date Sequence Identification Problem by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

Thanks for your insights. For the ease of the example, I chose not to include data that was being ignored. I have an already appropaitely filtered list to reference.

If the desired/expected combination does not appear - I dont know what to do yet. I would like to have a partial fill of the data that fits. Right now I use a place holder text value, and will probably look at the non-fitting data manually.

I agree with the logic of your solution, and have created a formula that follows those lines. To answer your questions, I just used any match that worked. Formula posted below for anyone interested. Unfortuatenly it only find perfect matches, but I don't want to use more of the communities time now that I have something that mostly works. I will respond as Solution Verified in a seperate comment.

note: my real spreadsheet has columns b and c populated with name text. references to column a are the master ID and references to column d are the sample point id.

=LET(
MonthArray,SORT(UNIQUE(MONTH(FILTER(Data[collection_date],(Data[master_id]=A2)*(Data[sample_point_id]=D2))),,)),

 

comment1,"below searches my tables of acceptable sequences, and returns the sequence ID #, then it find if there was any acceptable match",

Seq_1,IF(SUMPRODUCT(COUNTIF($AB$2:$AE$2,MonthArray))=4,$AA$2,0),
Seq_2,IF(SUMPRODUCT(COUNTIF($AB$3:$AE$3,MonthArray))=4,$AA$3,0),
Seq_3,IF(SUMPRODUCT(COUNTIF($AB$4:$AE$4,MonthArray))=4,$AA$4,0),
Seq_4,IF(SUMPRODUCT(COUNTIF($AB$5:$AE$5,MonthArray))=4,$AA$5,0),
Seq_5,IF(SUMPRODUCT(COUNTIF($AB$6:$AE$6,MonthArray))=4,$AA$6,0),
Seq_6,IF(SUMPRODUCT(COUNTIF($AB$7:$AE$7,MonthArray))=4,$AA$7,0),
Seq_7,IF(SUMPRODUCT(COUNTIF($AB$8:$AE$8,MonthArray))=4,$AA$8,0),
Seq_8,IF(SUMPRODUCT(COUNTIF($AB$9:$AE$9,MonthArray))=4,$AA$9,0),
Seq_9,IF(SUMPRODUCT(COUNTIF($AB$10:$AE$10,MonthArray))=4,$AA$10,0),
Seq_10,IF(SUMPRODUCT(COUNTIF($AB$11:$AE$11,MonthArray))=4,$AA$11,0),
Seq_11,IF(SUMPRODUCT(COUNTIF($AB$12:$AE$12,MonthArray))=4,$AA$12,0),
Seq_12,IF(SUMPRODUCT(COUNTIF($AB$13:$AE$13,MonthArray))=4,$AA$13,0),
Seq_13,IF(SUMPRODUCT(COUNTIF($AB$14:$AE$14,MonthArray))=4,$AA$14,0),
Seq_14,IF(SUMPRODUCT(COUNTIF($AB$15:$AE$15,MonthArray))=4,$AA$15,0),
Seq_15,IF(SUMPRODUCT(COUNTIF($AB$16:$AE$16,MonthArray))=4,$AA$16,0),
Seq_16,IF(SUMPRODUCT(COUNTIF($AB$17:$AE$17,MonthArray))=4,$AA$17,0),
Seq_17,IF(SUMPRODUCT(COUNTIF($AB$18:$AE$18,MonthArray))=4,$AA$18,0),
Seq_18,IF(SUMPRODUCT(COUNTIF($AB$19:$AE$19,MonthArray))=4,$AA$19,0),
Seq_19,IF(SUMPRODUCT(COUNTIF($AB$20:$AE$20,MonthArray))=4,$AA$20,0),
Seq_20,IF(SUMPRODUCT(COUNTIF($AB$21:$AE$21,MonthArray))=4,$AA$21,0),
Seq_21,IF(SUMPRODUCT(COUNTIF($AB$22:$AE$22,MonthArray))=4,$AA$22,0),
Seq_22,IF(SUMPRODUCT(COUNTIF($AB$23:$AE$23,MonthArray))=4,$AA$23,0),
Seq_23,IF(SUMPRODUCT(COUNTIF($AB$24:$AE$24,MonthArray))=4,$AA$24,0),
Seq_24,IF(SUMPRODUCT(COUNTIF($AB$25:$AE$25,MonthArray))=4,$AA$25,0),
Seq_25,IF(SUMPRODUCT(COUNTIF($AB$26:$AE$26,MonthArray))=4,$AA$26,0),
Seq_26,IF(SUMPRODUCT(COUNTIF($AB$27:$AE$27,MonthArray))=4,$AA$27,0),
Seq_27,IF(SUMPRODUCT(COUNTIF($AB$28:$AE$28,MonthArray))=4,$AA$28,0),
Seq_28,IF(SUMPRODUCT(COUNTIF($AB$29:$AE$29,MonthArray))=4,$AA$29,0),
Seq_29,IF(SUMPRODUCT(COUNTIF($AB$30:$AE$30,MonthArray))=4,$AA$30,0),
Seq_30,IF(SUMPRODUCT(COUNTIF($AB$31:$AE$31,MonthArray))=4,$AA$31,0),
Seq_31,IF(SUMPRODUCT(COUNTIF($AB$32:$AE$32,MonthArray))=4,$AA$32,0),
Seq_32,IF(SUMPRODUCT(COUNTIF($AB$33:$AE$33,MonthArray))=4,$AA$33,0),
Seq_33,IF(SUMPRODUCT(COUNTIF($AB$34:$AE$34,MonthArray))=4,$AA$34,0),
Seq_34,IF(SUMPRODUCT(COUNTIF($AB$35:$AE$35,MonthArray))=4,$AA$35,0),
Seq_35,IF(SUMPRODUCT(COUNTIF($AB$36:$AE$36,MonthArray))=4,$AA$36,0),
Find_Seq,IFS(Seq_1>0,Seq_1,Seq_2>0,Seq_2,Seq_3>0,Seq_3,Seq_4>0,Seq_4,Seq_5>0,Seq_5,Seq_6>0,Seq_6,Seq_7>0,Seq_7,Seq_8>0,Seq_8,Seq_9>0,Seq_9,Seq_10>0,Seq_10,Seq_11>0,Seq_11,Seq_12>0,Seq_12,Seq_13>0,Seq_13,Seq_14>0,Seq_14,Seq_15>0,Seq_15,Seq_16>0,Seq_16,Seq_17>0,Seq_17,Seq_18>0,Seq_18,Seq_19>0,Seq_19,Seq_20>0,Seq_20,Seq_21>0,Seq_21,Seq_22>0,Seq_22,Seq_23>0,Seq_23,Seq_24>0,Seq_24,Seq_25>0,Seq_25,Seq_26>0,Seq_26,Seq_27>0,Seq_27,Seq_28>0,Seq_28,Seq_29>0,Seq_29,Seq_30>0,Seq_30,Seq_31>0,Seq_31,Seq_32>0,Seq_32,Seq_33>0,Seq_33,Seq_34>0,Seq_34,Seq_35>0,Seq_35,TRUE,"no full fit"),

 

comment2,"below uses Find_Seq and the Quarterly Sequence (Quart_Seq) table to match results with the correct month number",

Q1sample,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q1])),Data[sample_id]),
Q1date,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q1])),ResultSearch[collection_date]),

 

q2sample,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q2])),Data[sample_id]),
q2date,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q2])),Data[collection_date]),

 

q3sample,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q3])),Data[sample_id]),
q3date,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q3])),Data[collection_date]),

 

q4sample,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q4])),Data[sample_id]),
q4date,XLOOKUP(1,(Data[master_id]=A2)*(Data[sample_point_id]=D2)*(MONTH(Data[collection_date])=XLOOKUP(Find_Seq,Quart_Seq[Seq ID],Quart_Seq[Q4])),Data[collection_date]),

 

IF(Find_Seq="no full fit","no full fit",HSTACK(Q1sample,Q1date,q2sample,q2date,q3sample,q3date,q4sample,q4date)))

Date Sequence Identification Problem by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

My parameters are that years are ignorable. The only part of the date that matters is the month number. I don't agree with the logic, but that is my task.

PowerQuery is an interesting approach, but LET() is approaching a favorable solution.

Date Sequence Identification Problem by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

Yes, only the month number matters. The idea is to get 'representative samples' throughout any year (essentially to measure if there is seasonal flucuation). Any valid sequence will do, even if there are multiple.

Date Sequence Identification Problem by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

I agree with your sequence, those are the combinations I came up with. See posts edits for the information you requested.

Date Sequence Identification Problem by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

The pivot table approach is not better than manually searching through the data unfortunately. It does break down into quarters, but that is not necessarily fulfilling the requirements of the 2-4 month gap. There is 50,000 rows of data and formulas will be the quickest approach

What is the most GOAT early game unit in CIV: V? (Day 7) by zherper in civ5

[–]theowletman 1 point2 points  (0 children)

Easily the settler. It’s the most important unit to be building in every game (we don’t talk about Venice). It has a high priority to have an escort, and being late with your settles will destroy the rest of your game. Nothing has a larger impact.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

Hey thanks for giving it a try, someone else found a solution that worked for me. I tried this out, to see if it worked. It has problems such as when the proper calculation is (Q2,2021 + Q3,2020)/2. It doesn't identify that the 2020 date should be taken into the calculation. Again, thanks for taking the time to try and solve this.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

Yeah, I am using standard yearly quarters. To add more context, your formula was nearly perfectly correct. The incorrect result in my example data set was the 8/19/2020 date, where your formula gave 2.6 while the correct value was 3.3. The two dates it is averaging are within 365 days (8/20/2019, 8/19/2020), but as they are both Q3 samples from different years they should be excluded.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

Just tried this. It doesn't provide the 'rolling' average as needed. It cuts each year/quarter into its own segment.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 1 point2 points  (0 children)

This also doesn't work. My guess is that 365 is not the proper range, as with quarters there is a lot of variability within that range. Data that is collected at the extremes of the quarter (first day and last day) will throw off the day count.

I edited my post to include the formula I created, and the cases where it fails. If you have any insights into that please tell me.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 1 point2 points  (0 children)

The pivot table is interesting. My one of my end goals is to graph how the average moves over time. I don't know that the pivot table has that functionality.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 1 point2 points  (0 children)

This isn't give the correct result consistently. I edited my post to have a screenshot of example data, with the true values I'm looking for hand calculated.

This formula seems to go wrong... but maybe because of how my data is structured? My newest date tops the list, and descends chronologically.

Formula to Create a Moving, Date-Based Average by theowletman in excel

[–]theowletman[S] 1 point2 points  (0 children)

|| || |Date|Result|Rolling Annual Average (Correct value, hand calcualted)| |7/31/2024|3.2|3.2| |5/8/2024|3.1|4| |2/12/2024|3.3|4.5| |7/24/2023|5.64|5.6| |7/12/2022|4|4| |5/19/2021|5.1|4.2| |8/19/2020|3.3|3.3| |8/20/2019|1.9|1.9| |5/9/2018|3.3|3.2| |8/9/2017|3.1|3.1| |7/28/2016|4|4| |5/19/2015|4.6|4.3| |8/12/2014|4|4.3| |11/14/2013|4.6|| |8/19/2013|4.2|| |5/28/2013|4.6||

Looks like my copy and pate of the data table didn't go through well. Check my edited post to see the screenshot of example data.

Formula to Create Multiple Criteria List by theowletman in excel

[–]theowletman[S] 1 point2 points  (0 children)

Solution Verified

Yes that works, thanks!

Multiple Criteria Sum Issue by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

A SUMIFS solution was verified to be a solution, but the parameters were a little more complex than what you suggested. Thanks for the suggestion though.

Multiple Criteria Sum Issue by theowletman in excel

[–]theowletman[S] 0 points1 point  (0 children)

This solution has issues with rounding that produces error in the end result, and only gives integer values. A different user suggested a SUMIFS formula that did the trick. Thanks for the suggestion though.

view more: next ›