Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh…..unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System) Below are numerical equivalent of Indian words Lakh – 1,00,000 Crore – 1,00,00,000…
Excel Quiz 21
Tips & Tricks 110 – Financial Functions – Calculate CAGR and AAGR
Many times we get posed with the question of calculating CAGR (Compounded Annual Growth Rate) and AAGR (Average Annual Growth Rate). For layman's not knowing what are these, following are good resources to introduce people to CAGR and AAGR. CAGR – http://www.investopedia.com/terms/c/cagr.asp AAGR – http://www.investopedia.com/terms/a/aagr.asp The formula to calculate CAGR is very simple. RATE function…
Tips & Tricks 109 – Macro to Change between A1 and R1C1 Notations
Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns. The option to do it very simple – File > Excel Options > Formulas > Uncheck R1C1 Reference Style But if that happens…
Solution – Challenge 23 – Make Bar Chart to Show Performance
— 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 =…