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,…
Solution – Challenge 25 – Prepare a Project Status Chart with Sliders
At the outset, let me thank How-to Make an Excel Project Status Spectrum Chart from where I learnt this kind of chart and inspired to set this challenge. My challenge is almost the same except that this is not spectrum but three distinct range of RAG are there. Below is a possible solution to Challenge…
Article 27 – Remove Leading Apostrophe
Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more…
Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters
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…