Excel Magic Trick 991: Formula To Create Sequential Numbers 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, ...

Sharing buttons:

welcome to exhale metric number 991 hey

if you want to download this workbook

989 to 993 click on the link below the

video in this video we need to create

these sequential numbers 1.1 1.2 all the

way down to 1.5 and then go to the next

sequential number 2.1 2.2 down to 2.5

and create a formula we can copy way

down now when looking at this sequence

of numbers here there's two parts

there's the one that's repeated many

times then the two that's repeated many

times and then there's 1 2 3 4 5 1 2 3 4

5 so sequential numbers that repeat two

different parts to this number now let's

build each individual part separately

and then we'll mash them all together in

a single formula so for this type of

sequence we can use the ceiling function

but we'll start with the inside part of

the formula the rows and the rows we can

use an expandable range so I'm gonna I'm

sitting in c7 saan the type seve dollar

sign 7 colon c7 that's an expandable

range that'll seven is locked this one

is not so when I control enter and copy

down you could see here oh the range is

7 to 10 and rows reports how many rows

there are in a range down here 7 to 11

is 5 now we have 1 to 10 we can divide

this number by 5 now f2 I highlighted

the whole range and in the active cell I

hit f2 divided by 5 so that 5 represents

how many times we're repeating each

sequential number all right so now we

have something we can work with point

two all the way up to 1 and then 1 point

2 all the way up to 2 we can simply use

the ceiling function and round all of

these up to the nearest integer which

would be 1 and all of these up to the

nearest integer which would be 2 so

highlight the range f2 that active cell

and I'm going to put this inside the

ceiling now in 2010 there's sitting math

which is great for negative numbers but


having a negative number so I'm just

using ceiling comma and the significance

I always want to go up to the next


control-enter absolutely beautiful

now for these numbers one two three four

five one two three four five we're going

to use the mod function and the mod

function gives us a remainder we

actually are going to use the same

formula number incrementer rose with an

expandable range so the dollar sign

seven : e seven control-enter double

click and send it down that gives us one

to ten now we don't want one to ten we

want zero to nine for this one so f 2-1

control-enter to populate that formula

all the way down

now what's 0/5 well it's zero what

meaning sorry the remainder zero divided

by five the remainder is zero 1 divided

by five the remainder is 1 so f 2 I'm

gonna use the mod function which gives

us the remainder that's the number the

divisor there's that 5 again right

because we're changing the sequence

every 5 numbers control-enter that gives

us 0 to 4 f2 we simply add one back in


so there we have our two formula

elements I'm going to actually copy the

cell ctrl C and paste it over here

notice our G dollar sign 7 g7 works fine

because that's referring to that cell

right there now this one I'm actually

gonna do a little trick here I'm gonna

highlight in edit mode ctrl C put this

into edit mode now we're gonna need to

have a period I need to join this

formula element with a period and then

the third formula element so I'm gonna

use the join symbol shift seven to get

that ampersand I need a period it's text

so I have to put it in double quotes and

then finally a second ampersand to join

my third formula element now notice this

cell reference is pointing to the wrong

way so I'm going to point to the edge

and with my move cursor I'm gonna click

and drag notice also most formulas will

give you a circular reference when

you're referring to the cell that the

formulas in but no wake rose and the

related columns functions don't do that

control enter double click and send it

down and you can copy this down you know

as far as you want and boom you're gonna

have your sequential numbers for more

about incrementing numbers there's a

playlist of 8 videos that show you all

sorts of cool tricks of income

incrementing numbers now one last thing

notice that this is spitting out text

and the reason why is when you join text

it becomes a text ring if for some

strange strange reason you wanted it as

a number I'm going to ctrl-c and ctrl-v

notice all of our cell references are

working we could do any math operation

on a number that stored as text and it

converts it back to a number now wait a

second when I'm looking at this I see

plus 1 isn't that a math operation but

check this out excels order of

operations plus is calculated before the

join symbol so what's happening is this

is an a temporarily a number but then

since this is calculated last the whole

thing is a text so no problem we're

gonna put parentheses around this whole

thing and then I'm gonna like add 0

whoops plus 0 and then ctrl enter oh we

can see that it's aligned over there

double click and send it down so if for

some reason you needed it as a number

there you go alright we'll see you next