Recent content by r1c1

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: