Deciphering Formula!

dannyok90

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
115
Hi All,

please see the formula below.

I obtained this formula some time ago and always use it to number cells.


Id like to decipher it so instead of being 1.1, 1.2, 2.0, 2.1 etc.


When I set the top level number as 000 it numbers 001, 002 or when the top level is 100 its 101, 102 etc


Any help would be much appreciated :)


=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A9,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))),OFFSET(A9,-1,0,1,1)&".1",LEFT(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A9,-1,0,1,1),LEN(OFFSET(A9,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))-1)))+1)))
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
I don't quite understand what this is supposed to do. I played with it and get the following but I don't understand how it's supposed to work. Can you provide some sample data and expected results?


Excel 2010
AB
91001
101110.1
111211.1
121312.1
131413.1
141514.1
Sheet1
Cell Formulas
RangeFormula
B9=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A9,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))),OFFSET(A9,-1,0,1,1)&".1",LEFT(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A9,-1,0,1,1),LEN(OFFSET(A9,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))-1)))+1)))
B10=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A10,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))),OFFSET(A10,-1,0,1,1)&".1",LEFT(OFFSET(A10,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A10,-1,0,1,1),LEN(OFFSET(A10,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A10,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))-1)))+1)))
B11=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A11,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))),OFFSET(A11,-1,0,1,1)&".1",LEFT(OFFSET(A11,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A11,-1,0,1,1),LEN(OFFSET(A11,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A11,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))-1)))+1)))
B12=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A12,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))),OFFSET(A12,-1,0,1,1)&".1",LEFT(OFFSET(A12,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A12,-1,0,1,1),LEN(OFFSET(A12,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A12,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))-1)))+1)))
B13=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A13,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))),OFFSET(A13,-1,0,1,1)&".1",LEFT(OFFSET(A13,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A13,-1,0,1,1),LEN(OFFSET(A13,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A13,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))-1)))+1)))
B14=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A14,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))),OFFSET(A14,-1,0,1,1)&".1",LEFT(OFFSET(A14,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A14,-1,0,1,1),LEN(OFFSET(A14,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A14,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))-1)))+1)))
 
Upvote 0
Hi matey,

Type 1 into a8 and Paste the code into a9 and drag it down, you can then change any of the numbers to a whole number so the proceeding are decimals.. it’s a way of sequentially numbering tasks and their sub tasks in projects..

Thanks,
Dan


Scott Huish said:
I don't quite understand what this is supposed to do. I played with it and get the following but I don't understand how it's supposed to work. Can you provide some sample data and expected results?


Excel 2010
AB
91001
101110.1
111211.1
121312.1
131413.1
141514.1
Sheet1
Cell Formulas
RangeFormula
B9=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A9,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))),OFFSET(A9,-1,0,1,1)&".1",LEFT(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A9,-1,0,1,1),LEN(OFFSET(A9,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))-1)))+1)))
B10=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A10,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))),OFFSET(A10,-1,0,1,1)&".1",LEFT(OFFSET(A10,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A10,-1,0,1,1),LEN(OFFSET(A10,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A10,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A10,-1,0,1,1),".","`",1))-1)))+1)))
B11=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A11,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))),OFFSET(A11,-1,0,1,1)&".1",LEFT(OFFSET(A11,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A11,-1,0,1,1),LEN(OFFSET(A11,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A11,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A11,-1,0,1,1),".","`",1))-1)))+1)))
B12=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A12,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))),OFFSET(A12,-1,0,1,1)&".1",LEFT(OFFSET(A12,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A12,-1,0,1,1),LEN(OFFSET(A12,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A12,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A12,-1,0,1,1),".","`",1))-1)))+1)))
B13=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A13,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))),OFFSET(A13,-1,0,1,1)&".1",LEFT(OFFSET(A13,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A13,-1,0,1,1),LEN(OFFSET(A13,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A13,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A13,-1,0,1,1),".","`",1))-1)))+1)))
B14=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A14,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))),OFFSET(A14,-1,0,1,1)&".1",LEFT(OFFSET(A14,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A14,-1,0,1,1),LEN(OFFSET(A14,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A14,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A14,-1,0,1,1),".","`",1))-1)))+1)))
Click to expand...
 
Upvote 0
That's a lot of formula for that. Try this:
=$A$8+(0.1*(ROWS($A$1:A1)-ROW($A$1)+1))

Copy down.


Excel 2010
A
81
91.1
101.2
111.3
121.4
131.5
141.6
151.7
161.8
171.9
182
192.1
Sheet1
Cell Formulas
RangeFormula
A9=$A$8+(0.1*(ROWS($A$1:A1)-ROW($A$1)+1))
A10=$A$8+(0.1*(ROWS($A$1:A2)-ROW($A$1)+1))
A11=$A$8+(0.1*(ROWS($A$1:A3)-ROW($A$1)+1))
A12=$A$8+(0.1*(ROWS($A$1:A4)-ROW($A$1)+1))
A13=$A$8+(0.1*(ROWS($A$1:A5)-ROW($A$1)+1))
A14=$A$8+(0.1*(ROWS($A$1:A6)-ROW($A$1)+1))
A15=$A$8+(0.1*(ROWS($A$1:A7)-ROW($A$1)+1))
A16=$A$8+(0.1*(ROWS($A$1:A8)-ROW($A$1)+1))
A17=$A$8+(0.1*(ROWS($A$1:A9)-ROW($A$1)+1))
A18=$A$8+(0.1*(ROWS($A$1:A10)-ROW($A$1)+1))
A19=$A$8+(0.1*(ROWS($A$1:A11)-ROW($A$1)+1))
 
Upvote 0
Hi,

This produces identical results as OP's Gigantic formula:


Book1
A
81
91.1
101.2
111.3
121.4
131.5
141.6
151.7
161.8
171.9
181.10
191.11
201.12
211.13
221.14
231.15
241.16
Sheet537
Cell Formulas
RangeFormula
A9=A$8&"."&ROWS(A$9:A9)
 
Upvote 0
Why not just =A8+0.1 filled down
 
Upvote 0
Fluff said:
Why not just =A8+0.1 filled down
Click to expand...

That would produce the same result as Scott's formula in Post # 4, where, after 1.9, the result becomes 2

The OP's original formula, after 1.9, becomes, 1.10, then 1.11, etc.
 
Last edited:
Upvote 0
jtakw said:
Hi,

This produces identical results as OP's Gigantic formula:


Book1
A
81
91.1
101.2
111.3
121.4
131.5
141.6
151.7
161.8
171.9
181.10
191.11
201.12
211.13
221.14
231.15
241.16
Sheet537
Cell Formulas
RangeFormula
A9=A$8&"."&ROWS(A$9:A9)
Click to expand...


All good stuff guys!

Tbh I’ve never really looked at the massive formula, just know it works? I haven’t tried your formula yet, but, I’ll take a look.. I guess the question now is really the same as my original question, how can I loose the “.” And just get it to start at 100, 101, 102 etc

I like the original formula in the sense that I can point to anywhere in the list and change it to a whole number and the rest follows...
 
Upvote 0
If you want sequential numbers, you don't need a formula.

For example, enter 100 in A8, 101 in A9, Select BOTH cells ( A8 and A9 ), drag down.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
GANNT Chart
Replies
1
Views
801
antrawson
A
C
  • Solved
formula to generate multilevel list numbering
2
Replies
14
Views
184
coffeenazi
C
B
  • Question
When is a number not a number? Match() does not recognize number.
Replies
7
Views
524
Tupe77
T
T
  • Question
UDF to reformat hierarchical numbering for sorting
Replies
5
Views
2K
tjdickinson
T
E
  • Solved
Spilled formula to return the min of a reducing range
Replies
3
Views
83
Fluff
Fluff

Forum statistics

Threads
1,215,029
Messages
6,122,760
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top