Below is a possible solution to the challenge – Challenge 45 – Number of Days Passed in a Quarter The formula to calculate number of days passed in a quarter is =A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)
Article 41 – Findings from the VLOOKUP and INDEX/MATCH shootout
Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time. Following is…
Challenge 47 – Generate Pentagonal Series
Pentagonal number series is following – 1, 5, 12, 22, 35, 51, 70, 92, 117, 145, 176…. You need to write an Excel formula which can be dragged down and generates the above sequence. The solution to this problem will be published after a month i.e. on 16-Aug-16.
Downloads 05 – Template 05 – Tornado Chart
Tornado chart is used to Sensitivity Analysis. It is one of the tools in Quantitative Risk Management phase of Risk Management. A good introduction about Tornado Chart is at https://en.wikipedia.org/wiki/Tornado_diagram The Tornado Chart template can be download from Template 05 – Tornado Chart
Tips & Tricks 140 – Multiple Hyperlinks within Excel Text Box
You created a Text Box and you put many words inside that say Yahoo, IBM, Microsoft etc…Now, you have given respective hyperlinks to them. But if you try to click on any hyperlink inside the text box, it will always open only one hyperlink. It is possible to circumvent this behavior by work-around. You can…
Solution – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?
Below is a possible solution to the challenge – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)? Enter the below formula in B1 as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula,…
Challenge 46 – Compute Numerological Sum for a Name
I had posted Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit. In this, I had explored how to add a number and arrive at a single digit. For example, if you have to add 8 + 7 the answer would be 15….
Downloads 04 – Template 04 – A Flexible Agile Burnup Chart
Download the template – Template 04 – Agile Burnup Chart Template v1.2 Download Password free version of this template – Template-04-Agile-Burnup-Chart-Template-v1.3_No Password When I posted the Burndown template, I promised that next template would be Burnup template. Unlike Burndown which shows remaining effort (stories etc.) to reach the goal, Burnup chart shows the effort spent…
Tips & Tricks 139 – Convert from Julian Dates to Excel (Gregorian) Dates
In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse. For 7 Digits Julian Dates, following formula should be used =DATE(LEFT(A1,4),1,RIGHT(A1,3)) For 5 Digits Julian Dates, following formula should be used depending upon which century (Note – Julian dates are most likely to fall…
Solution – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
Below is a possible solution to the challenge – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet 1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can…