In Numerology, it is often a task to add the digits till the result is a single digit. For example, 74 = 7 + 4 = 11 = 1 + 1 = 2 23 = 2 + 3 = 5 78 = 7 + 8 = 15 = 1 + 5 = 6 1234567 =…
Category: Tips and Tricks
Tips & Tricks 118 – Stop Auto Creation to Hyperlinks
We all know that if you enter a web address like www.microsoft.com, http://www.microsoft.com or even e mail address like admin@eforexcel.com, Excel converts these to Hyperlinks which can be clicked. Sometimes, we don't want this behaviour and we want to stop this – Trick 1 – After entering a web address / e mail address, press…
Tips & Tricks 117 – VBA – How to Count a particular character in a String
When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string – =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a","")) In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be…
Tips & Tricks 116 – Count the Number of Words in a Cell / Range
Suppose you have been given the following and you need to count the number of words in a cell or in a range. Formula for calculating number of words in a cell – =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"") Formula for calculating number of words in a range – =SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))+(TRIM(A1:A100)<>""))
Tips & Tricks 115 – Insert Fixed Current Date and Current Time
We all are aware about today() and now() formulas which insert current date and current date/timestamp. But these change with every recalculation of your worksheet. But if you want to enter the current date and time which doesn't change with recalculation i.e. it gets fixed, then following Excel shortcuts can be used. Current Date –…
Tips & Tricks 114 – COUNTIF for non-contiguous range
All of us love COUNTIF. And it is very easy to do – just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula…
Tips & Tricks 113 – Drag and Drop a Picture in Excel Sheet
Try dragging and dropping a picture in a worksheet – What happens. You will notice a plug sign which means that a picture will be added. But when you release the cursor to drop the picture in Excel sheet nothing happens. Reason – Excel doesn't support dragging and dropping the picture in a worksheet. Now,…
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,…
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…