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…
Category: Challenges
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…
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.
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,…
Solution – Challenge 26 – Find Sum given multiple OR conditions across columns
Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns. Put following formula for the result – =SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+ COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20)) The workbook illustrating the solution can be downloaded from Solution – Challenge 26 – Find Sum given multiple OR conditions across columns.
Challenge 28 – Create Pascal's Triangle
Pascal Triangle is very famous in number theory. Interested persons can read about Pascal's triangle at following Wikipedia article – https://en.wikipedia.org/wiki/Pascal%27s_triangle Anyway, whether read it at Wikipedia or not, below is Pascal's triangle. One look at it and you will understand the pattern. Now, the challenge before you is following – 1. Put 1 in…
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…
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 =…
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…