# 1.1, 1.2, 1.10, 1.19....

#### Udi Heller

##### New Member
Hi

I need to number a development spec on excel and need to numbering the entries.

Using =IF(B2="","",MAX(\$A\$2:A2)+0.1)

i can do 1.1.... 1.9, and than 2.0

but i need 1.1, 1.2... 1.10,... 1.15... and no on until there is a row empty, and than it jumps to the next series (2.0, 4.0... 10.0 and even 45.10 for example)

How can i change the formula to make it so?

10x,
Udi

#### shrivallabha

##### Excel Ninja
Welcome to Chandoo Org forums!

Your example is bit unclear. Can you expand series clearly and show exactly what you need?

#### Udi Heller

##### New Member
Hey

There is a list compiles from developments stages. each feature is numbered. 1.1, 1.2 etc. the code i gave earlier allow me only to work 1.1, 1.2..... 1.9 and than it jumps to 2.0,

what i need for like that

1.1
1.2
....
1.11
1.12

Line break? (empty row)
2.0
etc

10x !

#### Udi Heller

##### New Member
Will appropriate any one who will help me .... 10x

#### vletm

##### Excel Ninja
Udi Heller
for B3 =IF(B2="",INT(B1)+1.1,B2+0.1) and copy down.
(Note: max 'decimal' is .9)

#### Attachments

• 33.1 KB Views: 4

#### Udi Heller

##### New Member
Hi Vietm

10x for the help!

however, it dosent do what i need :-(

the first example i dont really understand, it dosent work so will for me
( =IF(B2="",INT(B1)+1.1,B2+0.1) )

for the second, u r using 3 columns, where the third one simply put together the other 2 in the format of X.Y

what i meant is a stand alone column, where the A2 is 1.0 (im writing it)
and starting A3, u have 1.1, 1.2 .... etc, without the limit of 1.9 and than 2.0, but it goes on with 1.11 etc

and where there is an empty row, the formula knows it needs to change the prefix to the next number, 2.0.... etc, again, without the limit of 9.X but it go go on to even 45.X

Any one can be kind and help with the formula for this ? i am sure u can

10x a lot
Udi

#### vletm

##### Excel Ninja
Udi Heller
the 1st: it works as written.

the 2nd: Did You mention somewhere that You can use only one column?
Sheet has many columns ...

Ps. Did You change rules?
eg 1.1 2.1 3.1 could be the possible after empty cell? or something else now?

Please, make crystal clear rules, what would You like to get.

#### Peter Bartholomew

##### Well-Known Member
A somewhat whimsical little number that may amuse!

#### Attachments

• 16.5 KB Views: 9

#### Udi Heller

##### New Member
Hey again

10x for the nice example of a notebook

As for what i need, i really tried to make this work but i cant
=IF(B2="",INT(B1)+1.1,B2+0.1)

it just giving me an error or 1.1 on all rows (A) and i dont know how and what should i change. i am not an excel programmer.

As for what i need, i will to be more specific

1. just one numbered column of the features for development (A1 ...)

2. start value for every new series: X.0

3. each series can go from X.0 to X.99

4. new (following) next series start when there is a black ROW (1.1, 1.11, blank row? 2.0)

5. the series can go up to 99.x

Hope this will help creating the appropriate formula

10x very much !

#### bosco_yip

##### Excel Ninja

In A2, enter :

=IF(B2="",MAX(A\$1:A1)+1,IF(B2<>"",LOOKUP(9^9,A\$1:A1)&"."&COUNTIF(A\$1:A1,LOOKUP(9^9,A\$1:A1)&"*")+1,""))

and, select A2 >> Custom cell format, enter: ;;;@

then, copied down

Regards
Bosco

#### Attachments

• 11.7 KB Views: 6

#### Udi Heller

##### New Member
Hey Bosco,

It doesn't work for me :-(

Both excel 2013 and 2016

I can c your work, it is what i need, but i am unable to replicate it

Or the spreadsheet it dosent do anything, or it shows the formula as it is in the rubric, without the end result or simply the number 1 (and i write 1 on A1, than A2 will show 2 an so does A3 and A4 (all 2)

i do change the format cell to ;;;@

i repeat what ever u did, and it still dosent work.

I dont know why and if i am doing something worng?

10x

#### shrivallabha

##### Excel Ninja
See if below idea is acceptable.

In cell A2: type 1.1 manually.

In cell A3 put following formula:
=IF(B3<>"",IF(B2<>"",CONCATENATE(INT(A2),".",RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99)+1),INT(LOOKUP(2,1/\$A\$2:A2,\$A\$2:A2))+1.1)&"","")

and copy down as much as you need.

#### bosco_yip

##### Excel Ninja
Hey Bosco,

It doesn't work for me :-(

Both excel 2013 and 2016

I can c your work, it is what i need, but i am unable to replicate it

Or the spreadsheet it dosent do anything, or it shows the formula as it is in the rubric, without the end result or simply the number 1 (and i write 1 on A1, than A2 will show 2 an so does A3 and A4 (all 2)

i do change the format cell to ;;;@

i repeat what ever u did, and it still dosent work.

I dont know why and if i am doing something worng?

10x
Upload your worksheet and let me see how did you used the formula

Regards
Bosco

#### Udi Heller

##### New Member
Hi Shrivallabha and Bosco,

your both example, i am sure, i gr8, but they just want apply on my excel :-( i am trying everything in every form i can think of. Shrivallabha just leave the cell blank, and the other one is giving 1 or error

C attachment. 10x !

#### Attachments

• 20.3 KB Views: 8

#### shrivallabha

##### Excel Ninja
Hi Shrivallabha and Bosco,

your both example, i am sure, i gr8, but they just want apply on my excel :-( i am trying everything in every form i can think of. Shrivallabha just leave the cell blank, and the other one is giving 1 or error

C attachment. 10x !
Numbers will correct if you input something in Feature column as formula currently relies on Column B content. See attached file.

#### Attachments

• 21.1 KB Views: 7

#### Udi Heller

##### New Member
i can assure u all that on the original workbook there more more than 1000 rows of data so it not that b is empty, but still i am strugeling

well, never mind, i realize it will not help me when i will sort one the collumes since excel will simply keep the same numbering and will not allow 2.1, 1.4, 11.6 one after the other and will always rearrange the record by 1.1, 1.2 etc. so it dosent help me

i will do the numbering manually. how ever i came to another problem...

i need help with custom format of a cell

i need
1.1, 1.2, ...., 1.19,... 2.0... 2.19

however, all the pre defined allow me to do
1.10, 1.20,... 1.11
or 1.1, 1.2... 1.10 becomes 1.1 again :-(

what can i do ?

10x a lot everyone
Udi

#### Peter Bartholomew

##### Well-Known Member
I took the lazy way out to move the workbook behaviour closer to your defined requirements. Because of the strategy used to build functionality, I was simply able to replace the data input range 'heading.level' by the formula

= IF( ISTEXT(Heading.text), 1, 2 )

where Heading.text is column 7 (or G as it is quaintly known to spreadsheeters that have their own unique way of counting ). This reduces the heading level array to the role of a helper field and I am sure it could be eliminated with minimal effort.

#### Attachments

• 17.9 KB Views: 8

#### GraH - Guido

##### Well-Known Member
A somewhat whimsical little number that may amuse!
Stunning visual deception...

#### Peter Bartholomew

##### Well-Known Member
Hi @GraH - Guido

Thanks for the comment. It sometimes surprises me how much human perception is influenced by graphical presentation considerations such as font size and the placement of information. Spreadsheets are often a case of 'Good sums, lousy journalism'; the message simply gets lost.

The attached is the same file but what one now sees now is tabular information held within a series of blocks whereas before is was a text document bounded in size. In reality it is a single Excel Table that grows gracefully as data is added but it lacks the jazzy stripes that herald "I am one of these new-fangled tables things that real spreadsheeters don't use".

Not quite; the level 1 numbering increments when the body text is missing rather than when header text is present and the single-digit heading 1 numbers are no longer output.

#### Attachments

• 15.7 KB Views: 6