Generating a series of numbers

In some of my earlier posts, I have manually populated some cells with a series of numbers (numbers 1-4 were manually populated in the post multiple extraction through vlookup). Here is a way we can do away with this by using functions row() and column()

Lets say you want to populate 0 to 10 in a column in cells B5 through L5:
Enter B5=COLUMN()-COLUMN($B$5)

On dragging upto L5, it populates the cells with the series 0 – 10.

As you can figure out

• column() returns the column number of that particular cell
• If a reference is provided [eg. column(B5)] – it returns the column number of the referred cell – 2 in the above example

If we were to populate numbers in a row, the function row() could have been used. To populate cells A1 to A11 with numbers 0 to 10,

• A1 = row()-row($A$1) – drag till cell A11

’tis done!

§ I am going to leave an example for you to think about the utility of this. Also would use this function in my next post.

√ To sum first 10 natural numbers – 1 to 10 i.e Σ(1,2..10)

Enter this in any cell =SUM(ROW(A1:A10)) as an array formula i.e. ctrl+shift+enter instead of enter. This gives the result as 55

The formula is interpreted as SUM({1;2;3;4;5;6;7;8;9;10})

If you found this interesting or would like some more help on this, let me know 🙂

Read Full Post »

Frequency distribution

The objective is to create a frequency distribution of the data

The given list is in column A

Frequency is to be calculate for data in bins of 0-1, 1-2, 2-3 etc

1. Create bin in Column C {1,2,3,4,5} in above

2. D2:D6 is selected and frequency array formula is entered

{=FREQUENCY(A2:A12,C2:C6)}

Array formula is entered with ctrl+shift+enter

3. The results are displayed in D2:D6

Read Full Post »

Identifying duplicates in a list

Tips for identifying duplicates in a list

Lets say the list is

Tip#1: Conditional formatting
Go to conditional formatting box and enter
“Formula is” =COUNTIF($A$1:$A$8,A1)>1
Format the box with a different colour (yellow in above)
Format paint for all cells A1 to A8 with ‘format painter’ brush

Tip#2: Array formula
Enter an array formula
{=IF(MAX(COUNTIF(A1:A8,A1:A8))>1,”Duplicates”,”No Dups”)}
Array formulas are entered by ctrl+shift+enter
In this example, formula gives result as Duplicates

Tip#3: Flag
In cell B1 enter
=IF(COUNTIF($A$1:$A$8,A1)>1,”Dup”,”No_dup”)
Drag the formula to B8
This flags off the dup entries

Tip#4: create an array of unique entries

Select A1:A8, click Data -> Filter -> Advanced Filter… ->Unique records only

This list is without any duplicates

Read Full Post »