— The solution workbook related to this challenge solution can be downloaded from Solution – Challenge 23 – Make Bar Chart to Show Performance — Below is a possible solution to the challenge Challenge 23 – Make Bar Chart to Show Performance 1. Download the workbook from Salesman Performance Challenge 2. Select range A1:C7 3….
Challenge 25 – Prepare a Project Status Chart with Sliders
This time challenge is to prepare following chart for Project Status. I am not giving any Excel sheet for this challenge. Just think and be creative. You may post your solution in comments section. Note – Solution to this challenge will be published after 1 month i.e. on 11-Oct-15.
Article 25 – Reverse FIND / SEARCH & MID Function
Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet. Hence, the only option before us is to build them through formulas. Let's build a reverse FIND…
Solution – Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves
Below is a possible solution to the challenge – Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves If A1 and A2 contain the start and end dates, put following formula and drag down – =IFERROR(AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$2)))/((TEXT(ROW(INDIRECT($A$1&":"&$A$2)), "ddd")="Fri")*(TEXT(ROW(INDIRECT($A$1&":"&$A$2)),"dd")="13")),ROWS($1:1)),"") A workbook illustrating the same can be downloaded from Solution – Challenge 22 –…
Solution – Challenge 21 – Male Female Pie Chart with Pictures
— The workbook related to this solution can be downloaded from Solution – Challenge 21 – Male Female Pie Chart with Pictures — Below is a possible solution to the challenge – Challenge 21 – Male Female Pie Chart with Pictures Download the files related to this challenge as given in the problem – Male_Female_Pie_Chart…
Tips & Tricks 108 – Extract Date and Time from Date Timestamp
Suppose you have a date timestamp value in cell A1 A1 = 06/14/15 10:15 PM And you want to extract date and time out of this. To extract date, use following formula and format the result cell as date = INT(A1) To extract time, use following formula and format the result cell as time =…
Tips & Tricks 107 – Autofill on a Filtered List
Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill – Article 7 – Generate a Sequence of Numbers Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The…
Excel Quiz 20
Article 24 – Convert a Number to a Month Name
Use below formula to generate named 3 lettered month like Jan, Feb….Dec =TEXT(A1*30,"mmm") Replace "mmm" with "mmmm" to generate full name of the month like January, February….December in any of the formulas in this post.
Challenge 24 – Sum a Range Conditionally where Range Inputs are variables
— Excel related to this challenge can be downloaded from Challenge – Sum a Range Conditionally — Suppose, you have values in column A and D1 and D2 contains the range references. D3 contains the condition value for greater than. In this example, you will need to sum up between A4 and A8 where values…