sequential text and numbers problem

  • Thread starter Thread starter patrickmcdiver
  • Start date Start date
P

patrickmcdiver

I need a formula that will help me generate a sequential list. My values are
as follows note that the "+" is strictly text and has no mathematical
significance): 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13, .... The number
before and after the "+" change per each report but the number after the "+"
always increases by 5. The number before the "+" increases by 1 once the
numbers after the "+" exceed 100 (so that there are only ever 2 space values
after the "+").

Someone please help as I have to generate hundreds of these reports.

FYI the numbers reference a station on a CAD drawing and I will later be
inputting voltage potential data in the cells adjacent the stations so that
the report will show a location corresponding to a potential.
 
enter:

=IF(ROW(A73)+4*(ROW(A1)-1)>100,1,0) & "+" & RIGHT(ROW(A73)+4*(ROW(A1)-1),2)


and copy down.
 
If the list is required in a column the following is a suggestion, where x is
the starting row number -1 (so if list starts in row 5 x=4). Works
independandantly of which column the list is in.

=IF(INT((((ROW()-x)*5)+3)/100)=0,"",INT((((ROW()-x)*5)+3)/100))&"+"&IF(LEN(MOD(((ROW()-x)*5)+3,100))=1,"0"&MOD(((ROW()-x)*5)+3,100),MOD(((ROW()-x)*5)+3,100))

There is probably a neater way, but appears to work...
 
Ooops didn't realise significance of 0 for items less than 100, makes it less
complex though...

=INT((((ROW()-x)*5)+3)/100)&"+"&RIGHT(MOD(((ROW()-x)*5)+3,100)+100,2)
 
Back
Top