Suppose, you have been given a year in A1 (Say A1 = 2016) and you need to determine the number of working days in a Year, then your formula to determine number of working days would be – =NETWORKDAYS("1JAN"&A1,"31DEC"&A1) The above formula is based on the fact that Saturdays and Sundays are weekends. Starting Excel…
Solution – Challenge 46 – Compute Numerological Sum for a Name
Below is a possible solution to the challenge – Challenge 46 – Compute Numerological Sum for a Name The formula to calculate Numerological Sum for a Name would be – =MOD(SUMPRODUCT(MOD(CODE(MID(SUBSTITUTE(LOWER(A1)," ",""), ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1))+2,9)+1)-1,9)+1
Challenge 48 – Whether a Sentence is Pangram or Not
This time, I decided to pose a challenge on English words. A Pangram is that sentence in English which uses all 26 alphabets of English language at least once. A very common example is the very famous sentence "The quick brown fox jumps over the lazy dog" which contains all alphabets of English language at…
Tips & Tricks 141 – How to Disable Quick Analysis Tool in Excel 2013
In Excel 2013, we have a new feature called Quick Analysis Tool. If you select a range and press CTRL, you are presented with Quick Analysis Tool. It offers various commonly used functions which you can perform. Few people don't like this. To disable it permanently – File > Options > Uncheck the Red Zone…
Solution – Challenge 45 – Number of Days Passed in a Quarter
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,…