# Recent content by r1c1

1. ### helping to a filmmaker friend

sounds fishy... let's not be the forum that launches a ddos. :D
2. ### 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. ### 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. ### 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. ### 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. ### Help to add only numeric value in a cell

You can use the formula in #2, just change " GB" to " MB".
7. ### convert roman numerals to numbers

Interesting formulas everyone. How about ARABIC()? It works in Excel 2013 or above.
8. ### 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. ### 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. ### 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. ### 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. ### 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. ### Formula to pull data that is within parenthesis in a field?

@Villalobos brilliant use of array find with {-1,1} multiplication.. :)
14. ### 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. ### 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: