View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default octogonal repeat?

I am looking for a cell function that allows me to icrease at a
set top point of .8
EX. 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 2.0, 2.1, 2.2, 2.3 etc.


I am not sure if this formula will do exactly what you want... it starts
at 1.0, not 1.1, and then continues as you have indicated.

=1+INT((ROWS($1:1)-1)/9)&"."&MOD((ROWS($1:1)-1),9)

The reason it does this is to keep each major "chapter" (the number
before the decimal point) the same length; otherwise the 1's group will
have 8 numbers in it (1.1 to 1.8) whereas all the other groups will have
9 numbers in them (x.0 to x.8).


However, if you really wanted to start at 1.1 and continue as you showed,
this minor modification to the formula I posted will do that...

=1+INT((ROWS($1:2)-1)/9)&"."&MOD((ROWS($1:2)-1),9)

I forgot to mention in my first post, but I am sure you figured it out,
put this formula in **any** cell you want and then copy it down as far as
needed.


One final thought... I see the other solutions offered are generating
numerical values (my formulas as written generate text values)... if you
want numerical values, just encase either of my formulas in parentheses and
precede that with the double unary (minus sign) symbol....

To start at 1.0
***************
=--(1+INT((ROWS($1:1)-1)/9)&"."&MOD((ROWS($1:1)-1),9))

To start at 1.1
***************
=--(1+INT((ROWS($1:2)-1)/9)&"."&MOD((ROWS($1:2)-1),9))

Rick