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.
Author: Vijay A. Verma
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…
Challenge 45 – Number of Days Passed in a Quarter
This time the challenge is – If a date is given, what would be the formula to find the number of days passed in a quarter. If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71…
Article 40 – Order of Operations in Formula
Follow the BODMAS rule B – Brackets (Parentheses) O – Orders (Powers/Exponents) D – Division M – Multiplication A – Addition S – Subtraction