• 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


  • 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. r1c1

    helping to a filmmaker friend

    sounds fishy... let's not be the forum that launches a ddos. :D
  2. r1c1

    SumProduct Quarterly from Monthly Data

    @StarMusk10 You do not need SUMPRODUCT for this. You can use SUMIFS (which offers simpler syntax). For example, this formula will get "None" values for Fy19 Q1 in Unit "1". =SUMIFS(C$2:C$55,$B$2:$B$55,">=1-Oct-2018",$B$2:$B$55,"<1-Jan-2019",$A$2:$A$55,1) Please find attached file with...
  3. r1c1

    Best chart to show contribution to total value?

    Follow what @Chihiro said. If you need some inspiration, here are few things you could try. Make a pareto chart - show 20% contracts that contribute 80% (or most) amount and combine others to a single column "Others" Show contracts in descending order of value in either a table (with some...
  4. r1c1

    Source vs Uses of funds - Excel dashboard

    @yaswanth92 Good question. I wrote a blog post about this with 14 different options. Check it out here: https://chandoo.org/wp/charts-for-source-vs-use-of-funds-data
  5. r1c1

    dashboard chart

    Please read the corresponding article for this chart here: https://chandoo.org/wp/interactive-sales-chart-in-excel/ There is a video too, explaining how its all done :)
  6. r1c1

    Help to add only numeric value in a cell

    You can use the formula in #2, just change " GB" to " MB".
  7. r1c1

    convert roman numerals to numbers

    Interesting formulas everyone. How about ARABIC()? It works in Excel 2013 or above.
  8. r1c1

    Measure to Calculate occurrence multiple times

    @Brij... You can use a simple DISTINCTCOUNT formula to count how many distinct prices are there in a row context. add the measure unique count:=DISTINCTCOUNT(Table1[Unit Price Each]) remove unit price each from the row label area Add the unique count measure to report This will tell you how...
  9. r1c1

    Assigning categories to different keywords from a text

    @LOGi Welcome to Chandoo.org forums and thanks for posting your question. Very interesting indeed. For better data management purposes, I recommend storing both names in 2 columns. But assuming the name is one cell and always in first name space last name format below solution should work...
  10. r1c1

    Create custom column using date & hour

    I hope your formula from post #3 solves the Power Query issue. For DAX part, you can use FORMAT formula. Assuming [date] column has the date & time in the format you mentioned, you can use =FORMAT([date], "yyyymmddhh")
  11. r1c1

    Formula to pull data that is within parenthesis in a field?

    @devsmom73 As you say you need the 4 digits inside parenthesis, you can use this too. =MID(A4,SEARCH("(*)",A4)+1,4)
  12. r1c1

    Rounding and SUMPRODUCT Function

    finally woke up. Here is another one. =ROUND((SUMPRODUCT(--(C5>={356;396;494;712;1283;1539;3461}),(C5-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1}))+0.17,0) Alternatively, the one suggested by @NARAYANK991 should work too.
  13. r1c1

    Formula to pull data that is within parenthesis in a field?

    @Villalobos brilliant use of array find with {-1,1} multiplication.. :)
  14. r1c1

    Freeze column reference in dynamic table for VLOOKUP

    Alternatively, you can also just Copy Paste instead of dragging. This will keep the structural refs intact while changing relative refs as you would expect.
  15. r1c1

    Visualizing Years, Companies & 4 financial variables in a single chart

    You can try a panel chart to explore such inter-relationships. I have used absolute values, but you can also try with indexation. That can show sharp changes compared to initial values in 2011 Both pictures embedded below. Absolute values trend: Indexed values trend:
  16. r1c1

    Rounding and SUMPRODUCT Function

    Interesting question @Michelle06 One option is to obviously add an extra condition by duplicating SUMPRODUCT, like this: =INT((SUMPRODUCT(--(C6>={356;396;494;712;1283;1539;3461}),(C6-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1})) +...
  17. r1c1

    Payback period

    An awesome formula indeed. Of course the =PERCENTRANK(A5:F5,,20)*MAX(A2:F2) one takes the cake. But if you need a quick way to get headache, the FORECASE(,IF(...)^2) is the one to go. Trying to understand this formula without testing it in Excel makes my head hurt. :DD:awesome:
  18. r1c1

    Group certifications

    @Ron Zanetti Thanks for your message. Yes, we offer participation certificates on all our courses. For more information email me at chandoo.d@gmail.com
  19. r1c1

    Animating Charts in PowerPoint

    Hi @chirayu Thanks again for this. I have scheduled the article to go live today here: http://chandoo.org/wp/2016/03/17/animated-charts-in-power-point/
  20. r1c1

    Offset, Loop, and Report Generation

    @ShawnExcel Thanks for the question. Although VBA can be used, I think you can also get the report with a simple formula. Here is the formula approach. I have set up an extra row to calculate the cumulative number and then used INDEX, MATCH to fetch corresponding report items. This assumes...
  21. r1c1

    Many many congratulations Chihiro for his first 1,000

    Congratulations @Chihiro Wishing you many more 1000s.
  22. r1c1

    Search & lookup function in Data Validation

    If you are trying to have auto complete / partial match based on user input, use Active X combo box. If I misunderstood your question, please upload a sample file and explain what you are trying to do so I (or someone else) can guide you.
  23. r1c1

    Search & lookup function in Data Validation

    Hi A!, I assume you are using this formula inside custom data validation as a rule. If so, data validation function must return TRUE / FALSE only. Change it to NOT(ISERROR(LOOKUP(2;1/SEARCH(C3;a1:a20);b1:b20))) and try.
  24. r1c1

    Freelance admin assistant @ Chandoo.org

    Hi Folks, I am looking for an administrative assistant for our online training programs. The job involves, - Monitoring email for successful payments from new students - Adding students to our online class - Helping students with password reset - Changing login details (name or email) -...
  25. r1c1

    VLOOKUP and cells with "*" NOT to be interpreted as wildcard

    Another alternative, probably the supposed way to do this. =VLOOKUP(SUBSTITUTE(B4,"*","~*"),$F$4:$G$15,2,FALSE) Essentially, to bypass the wild cards, you need to use ~. Since your original lookup values are already in the cells, you can use SUBSTITUTE to replace the *s with ~*.