Suppose, you have been given a string in A1 = "234, Washington D. C. @ 45609" The challenge before you is to write 2 formulas to count the number of 1. Alphabets 2. Numerals 3. Other Characters other than space For example, in above string, number of alphabets = 12 and number of numerals =…
Excel Quiz 22
Tips & Tricks 112 – Convert a Number into Years and Months
Suppose, you have been given a number into cell A1 say 26 and you want to display it as 2 Years and 4 Months, you can use following formula – =INT(A1/12)&" Years and "&MOD(A1,12)&" Months" Now, an user can become more demanding and he can say that if month is less than 12, then Years…
Tips & Tricks 111 – Extract URLs from Hyperlinks
In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster Now,…
Solution – Challenge 24 – Sum a Range Conditionally where Range Inputs are variables
Below is a possible solution to the Challenge 24 – Sum a Range Conditionally where Range Inputs are variables Put following formula for the result – =SUMPRODUCT((INDEX(INDIRECT(D1):INDIRECT(D2),,)>D3)*(INDEX(INDIRECT(D1):INDIRECT(D2),,))) The workbook illustrating the solution can be downloaded from Solution – Challenge 24 – Sum a Range Conditionally where Range Inputs are variables
Challenge 26 – Find Sum given multiple OR conditions across columns
— The Excel file related to this challenge can be downloaded from Challenge – Multiple OR Conditions — Given your data below, you need to find the total sales for cities given in F1 to I1 and zones given in F2 to I2. Either city has to match in F1 to I1 or zone should match…
Article 26 – Converting Amount into Words (Indian Currency – Rupees and Paise)
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…