Return a weeks of time to achieve a fixed target, best, Worst performed weeks and values.

[Mohammad Munawar]

Hi,

required a formula help which is calculate based on data/values contains in Col A & B in cell F3 Weeks to be taken for achieving a target, the target value is showing in Cell D4.

As well as in cell no F5& G5 required a formula which returns based on col A&B Over all best performed weeks (Top3)and sum the values incurred to same.

Based on col A&B calculate the Overall worst negative performed weeks return the same in Cell F7 and in G7 return the sum of incurred negative values where are values goes into minus.

In cell F9 return the weeks wherein no works are performed (Blank cells of Col B)

sample enclosed.

Thanks for the help.

[Bo_Ry]

Please put some manually calculated results.

My guess is
F3
=XMATCH(D4,MMULT(N(ROW(B3:B18)>=TRANSPOSE(ROW(B3:B18))),N(+B3:B18)),1)

F5
=TEXTJOIN("|",,INDEX(FILTER(SORTBY(A3:A18&TEXT(B3:B18," #,0"),B3:B18,-1),SEQUENCE(ROWS(A3:B18))<=3),,1))

F7
=TEXTJOIN("|",,INDEX(FILTER(SORTBY(A3:A18&" "&TEXT(B3:B18,"#,0"),B3:B18),SEQUENCE(ROWS(A3:B18))<=3),,1))

F9
=TEXTJOIN("|",,FILTER(A3:A18,B3:B18=0))

[Mohammad Munawar]

Thanks a lot Bo_Ry for your valuable response.


i herewith enclosed a sample sheet manually entered sample as requested for desired results for your reference.


Thanks,

[Bo_Ry]

Please try at

F3 Linear interpolate

Please Login or Register  to view this content.

F5
=TEXTJOIN("|",,FILTER(SORTBY(A3:A18,B3:B18,-1),SEQUENCE(ROWS(A3:B18))<=3))

F7
=TEXTJOIN("|",,FILTER(SORTBY(A3:A18,B3:B18),SEQUENCE(ROWS(A3:B18))<=3))

G5
=SUM(FILTER(SORT(B3:B18,,-1),SEQUENCE(ROWS(A3:B18))<=3))

G7
=SUM(FILTER(SORT(B3:B18),SEQUENCE(ROWS(A3:B18))<=3))

[Mohammad Munawar]

Perfect,solved

Thanks a lot Bo_Ry for your formulas and great effort.