Try.............
1] If you have Office 365 or Excel 2019 CONCAT function, use this in H2 and copied down :
=""""&CONCAT(INDEX($A$1:$F2,N(IF(1,IF({1,0},1,ROW(A2)))),MATCH(G2,A2:F2,0)+1)&" ")&""""
Or,
2] If you don't have CONCAT function, use this in H2 and copied down :
=""""&OFFSET($A2,ROW(A$1)-ROW(A2),MATCH(G2,A2:F2,0))&" "&OFFSET($A2,0,MATCH(G2,A2:F2,0))&""""
Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.
Then,
D2, formula copied down :
=SUMPRODUCT((A$2:A$10=A2)*(A$1:A$9=A$2:A$10)*N(+C$2:C$10)/(COUNTIF(A$2:A$10,A2)-1))
Try...………………...
1] In C2, formula copied down :
=IF(A2=A1,B2-B1,0)
2] In D2, formula copied down :
=AVERAGEIFS($C$2:$C$10,$C$2:$C$10,"<>0",$A$2:$A$10,A2)
Similar to Fluff's idea, another shorter formula to obtain the same result.
=0+TEXT(LOOKUP(9^9,C$14:C$37)+1,"[>11]1")
Regards
Bosco
Assume criteria housed in B1:F1 as per below table
In B2, formula copied across to F2 and all copied down ;
=MID(LEFT($A2,IF(C$1="",250,FIND(C$1,$A2)-1)),FIND(B$1,$A2)+LEN(B$1)+1,250)
Further to my posted formula in Post #.2,
Should you wanted to return multiple result in respect of the multiple criteria, you need TEXTJOIN function of which available in Office 365 or above.
Then,
In "Map" sheet F2, array formula (confirm pressing Ctrl+Shift+Enter instead of just Enter) copied down :
=IF($G2="Y",TEXTJOIN(", ",1,INDEX(Lookup!$D$2:$G$52,N(IF(1,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(B2,",","</b><b>")&"</b></a>","//b"),Lookup!$C$2:$C$56,0))),MATCH(C2,Lookup!$D$1:$G$1,0))),"")
and,
Trailing space in B12 should be removed
Regards
In "Map" sheet F2, formula copied down :
=IF($G2="Y",IFERROR(VLOOKUP($B2,Lookup!$C$2:$G$52,MATCH(C2,Lookup!$C$1:$G$1,0),0),""),"")
However,
1] Remove trailing space in B12
2] You have multiple value in cells B99, B243 and B248 of which the above formula will return blank
Regards
To choose the options in column C in the "Report" sheet if chosen in Column B
In "Report" sheet, select C2 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET(List!$B$19,COUNTIF(List!$C$2:$K$11,$B2)+COUNTIF(List!$L$2:$O$11,$B2)*2,,,COUNTA(CHOOSE(COUNTIF(List!$C$2:$K$11,$B2)+COUNTIF(List!$L$2:$O$11,$B2)*2,First_Half,Second_Half)))
>> OK
>> Finish
then,
Copied down
In "Report" sheet, select C2 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET(IF(MATCH($A2,List!$C$1:$O$1,0)<10,List!$D$14,List!$K$14),,1,,4)
>> OK
>> Finish
then,
Copied down
Regards
Try.......
1] "Source table" housed in Column A and B, and "Result table" housed in column D and E
2] In D2, formula copied right to E2, and all copied down until blank:
=IFERROR(INDEX(A$2:A$10,INT(ROW(A2)/4)*3+MOD(ROW(A2),4)-1),"")
Regards
To understand how does a formula work ?
Select your formula, then in the ribbon click "Formulas" >> "Formula Auditing" >> select "Evaluate Formula" >> click "Evaluate", Excel will step through the parts of the formula individually.
Regards
Try.............
In K12, array formula (ctrl+Shift+Enter) copied across and down :
=SUM(($C$1:$I$8=K$11)*N(+$B$1:$H$8)*TRANSPOSE($A12:$H12))
Display MoreHi Bosco_yip,
Thank you for your reply - I have tried to use the formula you provided but the "Total" rows come up with no value, any chance you.
Those references to column T slightly confuse me, any chance you could elaborate?
Thanks in advance
I tested your data in "Column T ", and forget bring it back to "Column O"
Therefore,
The formula in O8 should be in :
=IF(C8<>"Total",G8+N8,SUM(INDEX(O$7:O7,MATCH("zz",B$7:B7)):O7))
Sorry tor the confusing
Regards
I think your copy down formula in O8 will give wrong result,
your O25 formula result show in 261, but it should be in 142.
So,
Your O8 formula could be shortly revised in :
=IF(C8<>"Total",G8+N8,SUM(INDEX(T$7:T7,MATCH("zz",B$7:B7)):T7))
Regards
Or.............
In cell W38, enter this simply formula and copied down :
=SUMIFS(S:S,Q:Q,W$37,R:R,V38)
Regards
Or,
In N2, formula copied right to O2 and all copied down :
=0+REPLACE(G2,LEN(G2)-1,," ")
Then, select cells range >> "Format Cells" choose "Time"
Regards
Or try this formula solution for average of the 2 largest values separated by 3 weeks (21 days) or over.
=(MAX(C2:C14)+AGGREGATE(14,6,C2:C14/((INDEX(B2:B14,MATCH(MAX(C2:C14),C2:C14,0))-B2:B14)>=21),1))/2
Regards
Or,
B2, copied down :
=LEFT(A2,FIND("-",A2))&TEXT(LOOKUP(9^9,-RIGHT(A2,ROW($1:$100))),"[<10]00")
Regards
Or.............
In B13, formula copied down to B15
=IF(C13="","",INDEX(B$1:B12,MATCH(9^9,A$1:A12))&"."&COUNTA(INDEX(D$1:D12,MATCH(9^9,A$1:A12)):D13))
Then, select B13:B15 >> copy/paste the formula to B17, B21,B25, B37, B41,B45 and B49
Regards
Bosco
This modified formula solution similar to your post #4 formula
In H2, formula copied across right to L2 and all copied down :
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("║"&$A2,"NF‰","║"),"%2D (2A",""),"3A",""),"║",REPT(" ",99)),COLUMN(A1)*99,99))
Regards
Bosco