LinkBack URL
About LinkBacks
Hi,
Required a formula help to Extract and return a Min date and Max date based on the month and year basis as per the data available in Column A:C.
Sample sheet with expected results are enclosed.
Thanks,
Hi,
Required a formula help to Extract and return a Min date and Max date based on the month and year basis as per the data available in Column A:C.
Sample sheet with expected results are enclosed.
Thanks,
Hi, try:
=MIN(IF((MONTH($A$4:$A$23)=MONTH($E4))*(YEAR($A$4:$A$23)=YEAR($E4))*($B$4:$B$23=$F4)*($C$4:$C$23=$G4),$A$4:$A$23))
and change the MIN to MAX for the max.
FormulaFormula:
Please Login or Register to view this content.
Since you have 365, in H4:
=LET(dataset,FILTER($A$4:$A$23,($B$4:$B$23=F4)*($C$4:$C$23=G4)*(TEXT($A$4:$A$23,"mm/yyyy")=TEXT(E4,"mm/yyyy"))),CHOOSE({1,2},MIN(dataset),MAX(dataset)))
and copy down.
Rory
and to save a bit of time generating E, F & G:
=LET(a,A4:A23,b,$B$4:$B$23,bc,B4:C23,u,UNIQUE(bc),cu,COUNTA(u),d,UNIQUE(EOMONTH(+a,-1)+1),mod,1+MOD(SEQUENCE(cu,,0),cu/2),CHOOSE({1,2,3},INDEX(d,INT(SEQUENCE(cu,,1,1/(cu/2)))),INDEX(u,mod,1),INDEX(u,mod,2)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Many Thanks all of you for your amazing formulas,
Except post number#03 formula (but Slowdown a sheet) is working, remaining all formulas return a zeros in 45000 Rows data,
Smaller data all formulas are working well.
Can you show us in a file (save as xlsb)
Please try
and for fun Latest function for Excel Insider beta
Please Login or Register to view this content.
Take , HSTACK, VSTACK and 11 more
Please Login or Register to view this content.
Similar in approach to Czeslaw's but with fewer calculations.
Formula:Please Login or Register to view this content.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks