Search results

Welcome to the board. Please don't post formula that is not worked for you. And forward us a sample file with minimum 5 rows of source data, Tell us, what do you want? complete with requirements, criteria and expected result. Thank you

Happy to hear that you can do it by your own self, Then, you can dig deeper to understand more about the IF function. Let us Excelling to further study of the Excel other functions. God blessings God is so good to you.... giving you the gift.

Hi @arifanmol Please stick on "One post one question" Please open a new thread for your new question. This thread is closed.

Please inform us what are the requirements to obtain the result in Column G as the expected result in Column L Regards

Hi pradeepm, A lot of people want to help you, but no one understand what you want. Please sit down and write to us " What are you trying to do and give us the expected result as well." Have a nice day. God blessings God is so good to you.... giving you the gift.

Try, In B1, formula copied down: =IFERROR(MID(A1,FIND("""",A1)+1,FIND("^",SUBSTITUTE(A1,"""","^",2))-FIND("""",A1)-1),"")

You know Goal Seek and Excel Table already. Why not do it and study it by yourself?

Thank you p45cal for your advice. I herewith attached my revised shorten formula solution file. Of which inclusive single formula and separated formulae with Defined names.

Try, In J2, formula copied down: =IF(OR(D2=$A$3:$A$5),SUMPRODUCT($F$2:F2*ABS($H$2:H2)*($E$2:E2=E2))*(ISNUMBER(MATCH($D2:D2,$A$3:$A$5,0))),IF(D2="Sell",F2*(LOOKUP(1,0/(D$1:D1="Buy"),I$1:I1)/G2),IF(D2="Sold",LOOKUP(1,0/(D$1:D1="Bought"),I$1:I1)-F2*(LOOKUP(1,0/(D$1:D1="Bought"),I$1:I1)/G2),"")))

Or, In D2, copied down: =IF(ROW(A1)<=COUNTA(A$1:A$99),INDEX(A$1:A$10,MATCH(ROW(A1),0+MID(A$1:A$10,2,FIND("-",A$1:A$10)-2),0)),"")

Try, In B, enter formula: =TEXTJOIN(",",,FILTERXML("<a><b>"&SUBSTITUTE("@"&A2,"-","</b><b>")&"</b></a>","//b[string-length(.)=3]"))

Try this solution with helper column, Helper_Sum of Grp A & B >> =($I3+$M3)/24+($J3+$N3)/24/60+($K3+$O3)/24/60/60 YEARS >> =INT(G3/365) MONTHS >> =INT(MOD(Q3,365)/30) WEEKS >> =INT(INT(Q3-(INT(Q3/30)*30))/7) >>>>>>>>>>>>>> SECCONDS >> =((K3+O3)/60-INT((K3+O3)/60))*60

Please google: "How to use SUMIFS functions?" before you ask any question. Your post#1 question is solved. And "new thread for new question" This thread is closed. Regards

Try, In I7, formula copied down: =IF(G7>$G$4,0,IF(F7+G7>G$4,ROUND(F7/G$4,0)*G$4+G7,G$4))

Try, 1] In Output 1 F2, formula copied down: =TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99*(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))&"/"&IF(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),TRIM(LEFT(RIGHT(SUBSTITUTE(B2," ",REPT("...

Or You can shorten it using 1 IF function only: =IF((A2>0)*(B2>0)*(D2<1),1-D2,0)

Hi, Please use Sumifs function instead of 25 If function as per following set up. In Result G3, enter formula: =SUMIFS(C:C,A:A,E3,B:B,F3)

Try, In E2, formula copied down: =IF((A2>0)*(B2>0),IF(D2>1,0,1-D2),0)

This is your file screenshot. Your Question: "Can Offset formula help me?" Ans: "How to go? Formula cannot help." The answer is given above, so this thread is closed.

No, Excel is using top-down calculation way, you cannot put total on top of data if you are using Excel Tables. Otherwise you need VBA to do the automatic work in meet with your requirement, and you need re-submit your question to our VBA Marcos forum. This post is closed. Regards

Try, In B2, formula copied down: =IF(ISNUMBER(MATCH(A2,A$1:A1,0)),IFERROR(VLOOKUP(A2,A$1:B1,2,0),1),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2))) or, =IF(ISNA(MATCH(A2,A$1:A1,0)),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)),IFERROR(VLOOKUP(A2,A$1:B1,2,0),1))

Submit a sanitized data file with criteria and expected result please!

Try, In C8, non-array formula copied across right: =IF(D$4="","",IFERROR(MIN(MATCH(C$5,INDEX(PROB(COLUMN(D$4:$R$4)-COLUMN(C$4),D$4:$R$4/SUM(D$4:$R$4),,COLUMN(D$4:$R$4)-COLUMN(C$4))*SUM(D$4:$R$4),)),COUNT(D$4:$R$4)-1),0)+1)

Here is a non-array formula solution. In D2, formula copied down: =IF(C2<>"",INDEX(L$2:L$10,AGGREGATE(15,6,ROW(L$2:L$10)-ROW(L$1)/(L$2:L$10<>""),COUNTA(C$2:C2))),"")

Hi, Happy New Year! Here is the forum rule: https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/ 1. You needed to tell us: "what do you want to achieve?" Please give us your expected result and criteria. and resubmit your file stating the above mentioned. Note: Please...