• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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 !
 

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

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

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 :rolleyes:). 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

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

Top