1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Udi Heller, Aug 26, 2018.

  1. Udi Heller

    Udi Heller New Member

    Messages:
    11
    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
  2. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    Welcome to Chandoo Org forums!

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

    Udi Heller New Member

    Messages:
    11
    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 !
  4. Udi Heller

    Udi Heller New Member

    Messages:
    11
    Will appropriate any one who will help me .... 10x
  5. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Udi Heller
    for B3 =IF(B2="",INT(B1)+1.1,B2+0.1) and copy down.
    (Note: max 'decimal' is .9)
  6. vletm

    vletm Excel Ninja

    Messages:
    4,299

    Attached Files:

  7. Udi Heller

    Udi Heller New Member

    Messages:
    11
    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
  8. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.
  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    449
    A somewhat whimsical little number that may amuse!

    Attached Files:

  10. Udi Heller

    Udi Heller New Member

    Messages:
    11
    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 !
  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005
    upload_2018-8-27_13-33-56.png

    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

    Attached Files:

    Thomas Kuriakose likes this.
  12. Udi Heller

    Udi Heller New Member

    Messages:
    11
    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
  13. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    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.
  14. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005
    Upload your worksheet and let me see how did you used the formula

    Regards
    Bosco
  15. Udi Heller

    Udi Heller New Member

    Messages:
    11
    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 !

    Attached Files:

  16. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,918
    Numbers will correct if you input something in Feature column as formula currently relies on Column B content. See attached file.

    Attached Files:

  17. Udi Heller

    Udi Heller New Member

    Messages:
    11
    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
  18. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    449
    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.

    Attached Files:

    deciog and Thomas Kuriakose like this.
  19. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Stunning visual deception...
  20. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    449
    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.

    Attached Files:

Share This Page