Search results

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

@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...

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

@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

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 :)

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

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

@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...

@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...

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")

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

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.

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

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.

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:

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})) +...

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:

@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

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/

@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...

Congratulations @Chihiro Wishing you many more 1000s.

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.

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.

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) -...

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 ~*.