1. Put =CHAR(96+ROW()) in cell A1. 2. Drag down till A26 to generate a to z. 3. Put =CHAR(64+ROW()) in cell B1. 4. Drag down till B26 to generate A to Z. 5. File > Options > Advanced > Go down till you find Edit Custom Lists (You have to go down till bottom) and…
Tag: sequence
Solution – Challenge 17 – Sum if Y Appears More than One Time Consecutively
Below is a proposed solution for challenge Solution – Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum 1. Introduce a helper column in column D and put following formula in D2 and drag down – =IF(B2="N",0,IF(AND(A2=$F$2,A1=A2),1+D1,1)) 2. Put following formula in F3 =IF(ISNUMBER(MATCH(F2,A:A,0)),SUM(OFFSET(C1,MATCH(MAX(D2:D22),D2:D22,0), 0,-MAX(D2:D22),1)),"")
Solution – Challenge 13 – Generating Digit Product Sequences
A proposed solution is listed below for the challenge – Challenge 13 – Generating Digit Product Sequences Put a starting seed in A1. Put following formula in A2 and drag down – =A1+PRODUCT(INDEX(–MID(SUBSTITUTE(A1,0,1),ROW(INDIRECT("1:"&LEN(A1))),1),,)) For a seed value of 1 in A1, it will generate following sequence – 1, 2, 4, 8, 16, 22, 26, 38,…
Solution – Challenge 11 – Generate a Repeating Number Sequence – II
Below is a proposed solution for challenge – Challenge 11 – Generate a Repeating Number Sequence – II Put following formula and drag down – =ROUNDUP(ROWS($1:1)/3,0) This will repeat the sequence 111222333444555………..
Solution – Challenge 10 – Generate a Repeating Number Sequence – I
Below is the proposed solution for challenge – Challenge 10 – Generate a Repeating Number Sequence – I Put following in a cell and drag down – =MOD(ROWS($1:1)-1,5)+1 This will generate a sequence 1, 2, 3, 4, 5 and will repeat this.
Solution – Challenge 1 – Single Formula for Fibonacci Numbers
This is a possible solution to Challenge 1 – Single Formula for Fibonacci Numbers There can be many solutions to a given problem in Excel. Below are solutions proposed by me – A1 and drag down – =IF(ROW()<3,ROW()-1,SUM(OFFSET($A$1,ROW()-3,0,2))) =IF(ROW()<3,ROW()-1,SUM(INDIRECT("A"&ROW()-2&":A"&ROW()-1))) If you belong to that class where you worry about volatality of OFFSET and INDIRECT, use…
Challenge 11 – Generate a Repeating Number Sequence – II
Last time, I posed a challenge to generate a repeating number sequence. This time, there is small twist. The sequence which you need to generate is 3 times each number i.e. 111222333444555……….. The formula should be flexible enough to be put in any cell and be dragged down. Hence, if I put the formula in…
Challenge 10 – Generate a Repeating Number Sequence – I
This time challenge is to write a formula which can be dragged down to generate number sequence 1,2,3,4,5 and repeats this sequence again. The formula should be flexible enough to be put in any cell and be dragged down. Hence, if I put the formula in D4, dragging down should produce the below series. 1…
Article 7 – Generate a Sequence of Numbers
Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of…
Tips & Tricks 55 – Quickly Fill in Cells with Dates from M to N
1. Put the start date in the cell where you want your first date to be. Let's put 6/1/14 as an example. 2. Select that Cell and in Home Tab, go to Fill and Select Series 3. Select Series in Columns if you want Column to be populated (Most likely case). Put End Date in in stop…