Below is a possible solution to the Challenge 28 – Create Pascal’s Triangle. Put 1 in L2 and put following formula in C2 and drag right and down. =IF(IF(B2="",0,B2)+IF(D2="",0,D2)=0,"",IF(B2="",0,B2)+IF(D2="",0,D2)) This will generate following Pascal's Triangle. To make it better looking, you may use Conditional Formatting. Select C2 and U11 > Home tab > Conditional Formatting…
Article 30 – VBA – Approaches for Unique Count and Time Performance Results for the Same
As part of this article, we will look into various approaches for counting unique in VBA and also see what time do they take to determine the best approach on the basis of "Time Taken". I have used Charles William's MicroTimer for timing the time. https://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx We will see performance of these approaches with following…
Challenge 30 – Average Last 5 Numbers in a Range
The Excel file related to this challenge can be downloaded from Challenge – Average Last 5 Numbers Let's say that you have got a range like this. The range which can contain values is A1:A100. The problem is to pick up last 5 numbers and average them. Notice that there are blanks and non numbers…
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…
Excel Quiz 25
Solution – Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters
Below is a possible solution to the challenge Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters. The formula for counting number of alphabets =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),""))) The formula for counting number of numerals =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,""))) The formula for counting other characters =LEN(SUBSTITUTE(A1," ",""))-C1-C2 Where C and C2 are number of alphabets and numerals.
Article 29 – Traffic Lights Conditional Formatting for Project Status for Text R / A / G
We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text. We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light…
Challenge 29 – Reverse (Flip) a Number String
This time challenge is for flipping a number string. Excel VBA has got a function "StrReverse" which can be used to perform this task through VBA. The challenge is to do this through a formula. Let's say A1 = 70948 You formula should return a result of 84907 Note if you number string is 709480,…
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)<>""))