• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. B

    data from coloumn crieteria to in a cell.

    Maybe you don't have Textjoin function in your Excel. Textjoin function only available in Excel 2019, Excel 2021 and O365. And, if you still wanted to use formula, please download a Textjoin UDF by Google searching machine. Note: Without Textjoin function, it will become a lo.......ng...
  2. B

    data from coloumn crieteria to in a cell.

    Try, [D2]: =TEXTJOIN(" | ",,IF(A$2:A$12=A2,B$2:B$12,"")) Formula copied down.
  3. B

    Inconsistent chartview

    {0,0,0,0,0} means for 5 columns of 0. If no checkbox is ticked. And You may use this formula, If the checkbox more than 3 and the number of source rows more than 3: =IFERROR(INDEX(N10:R100,MATCH(TRUE,M:M,0),0),{0,0,0,0,0}) Or =IFERROR(OFFSET(N10,MATCH(TRUE,M:M,0)-1,0,,5),{0,0,0,0,0}) Regards
  4. B

    data from coloumn crieteria to in a cell.

    What way do you wanted? Formula or VBA?
  5. B

    Vlookup

    1] The shortest formula of p45cal is: =VLOOKUP(LEFT(A1,LEN(A1)-4),Criteria!A:A,1,0) But it contents 3 functions. 2] I think this formula is the fastest. =LOOKUP(1,-FIND(Criteria!A$1:A$11,Sheet1!A1),Criteria!A$1:A$11) The above using 2 functions and limited Lookup range and Lookup result...
  6. B

    Multiply the value mentioned post "'-" Sign

    It is a simple formula to adopt Filterxml and Textjoin function if you have Excel 2019 or Excel 2021. 1] Define 2 names as per attachment. 2} In Result column, C2 formula copied down: =TEXTJOIN("|",,Dig_1&Dig_2)
  7. B

    VLookup

    Open a new post for your new question. This post is closed. Regards
  8. B

    Resign my work in the forum

    Please give the reward to Mr. Narayank991, he is my pioneer. The following screenshot show he got the "Most messages" and "Highest reaction score" as by the date of this post.
  9. B

    Resign my work in the forum

    My Stroke (2 times) happened on last end May 2022, and having around 7 months resting. I found my body condition become worse, and some other serious illness discovered. So, I need to leave the forum in a longer time. I must resign from the forum. Hope I will see you all again. Bosco
  10. B

    Excel help with min/max and between values

    As mentioned in my post#04, please open a new thread for your 2nd question. This thread is closed.
  11. B

    IF function decision tree - best practices

    Q1: Is there any way to avoid this? Ans1: Pls check with and learn LOOKUP function family, like: Index/Match, Vlookup, Lookup, Offset...... Q2: Is this bad practice? Ans2: Yes
  12. B

    Excel help with min/max and between values

    Question: Say if the distance is 267 KM (fits between the criteria of A7) and 457 kgs (fits between the criteria of Col D), then the return value should be 3150.00 Ans: 1] Set up an Output table with Criteria and Result as per below image. 2] In J2, enter formula...
  13. B

    need a formula to get user name in column

    Try, In D18, formula copied down: =IF(B18="","",LOOKUP("zzz",C$1:C17))
  14. B

    How to convert 1-5 scale Likert scale to 0-10 scale

    @p45cal, 1] I thank you for your reply and let me learn a new thing of Likert scale. 2] I used your formula and built up a table as in below. 3] In B3, formula copied across =(B1-1)*2.5
  15. B

    If Month Is Between 2 Dates Return...

    A bit different idea approach, my way is based on the Start Date column F and Month column H. In S10 copied across and down: =IF($K10="","",IF(AND(S$9>=(EOMONTH($F10,-1)+1),COUNT($R27:R27)<$H10),$K10,"")) Regards
  16. B

    How to convert 1-5 scale Likert scale to 0-10 scale

    You always make your question unclear. Please give us a sample file, showing that: what you have? and what you want? Without that >> We reserve to close your post should you fail to give us a clear reply. Regards
  17. B

    Count the number of cells required to add up to the reference cell.

    1] This part: >> "107760-30061(B9)=77699-27321(C9)=50,378-27734(D9)=22,644-27433(E9)=-4,789" Q: What is the rule of deduction? your above example deducts 4 forecast amounts. And 2] This 2nd part: "So I would want the answer to be 3.83 Q: How do you get 3.83 ??? Then, It seems that your 2nd...
  18. B

    Count the number of cells required to add up to the reference cell.

    Welcome to the board. Here is my formula solution based on your formula concept, together with 2 columns helper. 1] Forcast of DOS (days) [F2]: =DATEDIF(1/1/2023,ROUNDDOWN(B$1-WEEKDAY(B$1,1)+1,0),"yd") 2] Forcast of DOS (inventory) [F3] copied down...
  19. B

    Adjust and modify an equation For Tips

    Please compare with the formula used of mine and yours: Yours: IFERROR, INDIRECT, ADDRESS, INDEX, MATCH and OFFSET (6 functions) Mine: IFERROR, OFFSET and MATCH (3 functions) So, Simple Offset and Match is enough to solve your quite complex question. Let us more Excelling by this new year...
  20. B

    Nested IF & VLOOKUP issue (solved)

    @Kaboom We all welcome you join in. Hoping you can step in further new world of Excel, and we all in Excelling by this group. Regards Bosco
  21. B

    Compair two pair of cells: function usage

    Hi, @ETAF 1] Your formula: =IF(B13="","", LOOKUP(2,1/(APOIO!$B$2:$B$1000=B13*1)/(APOIO!$C$2:$C$1000=C13*1),(APOIO!$D$2:$D$1000))) It is a good formula for 2 criteria Lookup. However 2] I just suggest a better, shorter and faster format to use Lookup function as similar to yours...
  22. B

    Adjust and modify an equation For Tips

    Or you can start it from row 3, the formula become: =IFERROR(OFFSET(Transfer!$A$3,MATCH(F2,N(OFFSET(Transfer!$A$1,ROW($3:$121),MATCH(D2,Transfer!$A$2:$I$2,0)-1+MATCH(E2,Transfer!$B$1:$BA$1,0)-1)),0),MATCH(D2,Transfer!$A$2:$I$2,0)+MATCH(E2,Transfer!$B$1:$BA$1,0)-1),"") or this shorter...
  23. B

    Adjust and modify an equation For Tips

    Because your file in the Transfer sheet hidden the row of 4 to 63.
  24. B

    Nested IF & VLOOKUP issue (solved)

    Please refer to the Example 1 of which you solved it using IF function. 1] And I proposed to use a 3D formula as show in Example 1: It required to set up a "Sheet name List" (H1:H5) and "Lookup result list" (I1:I5) Then, in C2 copied down formula...
  25. B

    Adjust and modify an equation For Tips

    Try, In G2, formula copied down : =IFERROR(OFFSET(Transfer!$A$63,MATCH(F2,N(OFFSET(Transfer!$A$1,ROW($63:$121),MATCH(D2,Transfer!$A$2:$I$2,0)-1+MATCH(E2,Transfer!$B$1:$BA$1,0)-1)),0),MATCH(D2,Transfer!$A$2:$I$2,0)+MATCH(E2,Transfer!$B$1:$BA$1,0)-1),"")
Back
Top