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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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 Question
GANNT Chart
Replies
1
Views
1K
antrawson
A
C
  • Solved
formula to generate multilevel list numbering
2
Replies
14
Views
2K
coffeenazi
C
B
  • Question Question
When is a number not a number? Match() does not recognize number.
Replies
7
Views
862
Tupe77
T
T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
288
Tarkemelion
T
T
  • Question Question
UDF to reformat hierarchical numbering for sorting
Replies
5
Views
3K
tjdickinson
T

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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