In 2017, I posted Excel Formulas Bible which was very much appreciated. Since then much water has flowed down the Excel river. Dynamic Array functions, XLOOKUP/XMATCH had been introduced. They have made life much simpler. Now, all formulas are treated as Array formulas, you need not enter them as Array formulas. So, 5 years down…
Tag: sequence
Tips & Tricks 181 – Insert Sequence / Serial Numbers in a Pivot Table
Sometimes, it is important to show sequence numbers such as 1, 2, 3…..and so on. If you use database technologies, this is known as index column. 1, 2, 3…..are also used to denote rank. Suppose you have a pivot table which is sorted in descending order for the value. For the sake of showing ranking, you…
Change Index at discontinuity – Power Query – Tips & Tricks 4
Power Query – Change Index at each discontinuity. Sometimes, you need to insert index but change is needed at when value in left column changes
Inserting a Sequence 1,2,3,1,2,3,1,2,3…..- Power Query – Tips & Tricks 3 –
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,2,3,4,1,2,3,4,1,2,3,4
Inserting a Sequence 1,1,1,2,2,2,3,3,3,4,4,4….- Power Query – Tips & Tricks 2
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,1,1,1,2,2,2,2,3,3,3,3 and so on.
Article 50 – SEQUENCE Function
SEQUENCE is one of the functions introduced in 2020 for Office 365 subscribers as part of Dynamic Array functions. I have published this article @ Linked. Click below SEQUENCE FUNCTION
Solution – Challenge 68 – Generate a Particular Sequence
Below is a possible solution to the problem Challenge 67 – Generate a Particular Sequence – This consists of 2 logic. Generate Triangular number. The formula for which is n*(n+1)/2 Generate Column Labels (A, B, AA, ZZ….) from numbers, the formula for which is =SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to…
Challenge 68 – Generate a Particular Sequence
Study the sequence below and you need to write a formula which when dragged down should generate the below sequence. For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only. You need to generate upto 100 entries. The solution to this challenge will be…
Solution – Challenge 61 – Generate Multiplication Table
Below is a possible solution to the challenge – Challenge 61 – Generate Multiplication Table Put following formula and drag right and down – =ROWS($1:1)*COLUMNS($A:A)
Challenge 61 – Generate Multiplication Table
This time, I want to set a challenge which is not difficult and useful for your kids. The challenge is to write a formula which can be dragged right and down to generate a multiplication table. The solution to this challenge will be published after a month i.e. on 1-May-17.