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 [...]

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 [...]

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 [...]

Read Full Post »