# Deciphering Formula!

#### dannyok90

##### Board Regular
Hi All,

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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)))

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

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

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

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)

Why not just =A8+0.1 filled down

Why not just =A8+0.1 filled down

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

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

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.

Replies
1
Views
473
Replies
5
Views
2K
Replies
2
Views
1K
Replies
7
Views
388
Replies
3
Views
398

1,207,203
Messages
6,077,032
Members
446,252
Latest member
vettaforza

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

### Which adblocker are you using?

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

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