In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar. To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs…
Tips & Tricks 156 – Get Workbook's Directory from Formula
If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2) Note – For this formula to work, you workbook must be saved at least once.
Downloads 12 – Template 10 – A Flexible Amortization Schedule Generator
This flexible Amortization Schedule Generator can be downloaded from Amortization Schedule Template Unprotected template – Amortization-Schedule-Template_Unprotected 1. You can specify Interest Rate not only annually but also weekly, monthly, quarterly and half yearly. Default is Yearly if nothing is specified. 2. You can specify Loan Period in weeks, months, quarters, half years and years. Default…
Tips & Tricks 155 – Gridlines not Visible, Help!!
You may try following one by one and verify your results 1. Update your Printer Driver to latest from internet (Or change your default printer driver to PDF/XPS/One Note) 2. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times). Home tab > Go to Paint Bucket and…
Challenge 59 – Clean the Problem Workbook Data
Download the the workbook from the below link. The Challenge before you is to write a formula to clean the data. If you perform any mathematical operation on the data, it will return #VALUE error. You need to clean the data through a formula. Challenge 59 – Problem Workbook The solution to the above problem…
Downloads 11 – Printable & Pin-up Sheet for Excel Functions
You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference – Excel Functions Quick Reference
Tips & Tricks 154 – Insert a Space after Each Character
You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro….
Solution – Challenge 58 – Make a Vedic Square
Below is a possible solution to Challenge 58 – Make a Vedic Square Put the following formula in C3 and drag right and down =MOD(C$2*$B3-1,9)+1 The Excel sheet having this solution can be downloaded from Solution – Challenge 58 – Make a Vedic Square
Excel Quiz 36
Tips & Tricks 153 – Sum only Visible Columns
You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2) If you hide the columns, the sum stays…