• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

series fill with same number twice

I need to series filll a column with the same number twice. For example A6 = 1,A7 =1, A8=2.,A9 = 2, etc. (ms xls 2007)
0
howcheat
Asked:
howcheat
1 Solution
 
AlanConsultantCommented:
A1 = 1
A2 = 1
A3 = if(A2=A1,A2+1,A2)

Copy A3 down.

Alan.
0
 
AlanConsultantCommented:
If you want them as values, and not formulae, do the above, then copy and paste to values.

Alan.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

for example to do this with 200 cells, fill the series to the first 100 cells, going 1,2,3, etc. then fill the next 100, starting again with 1,2,3. Select all 200 cells and sort ascending.

cheers, teylyn
0
 
Saqib Husain, SyedEngineerCommented:
You can change Alan3285's formula

=if(A2=A1,A2+1,A2)

to

=A1+1

If you don't have to use the value of the numbers you can fill series starting from 0.5 and increment by 0.5 and display with zero decimal places.

Another formula which can be used and then converted to values is

if starting in row 6 then
=INT((ROW()-4)/2)

if starting in row 7 then
=INT((ROW()-5)/2)


0
 
howcheatAuthor Commented:
Great resolutions - All!! Alan3285 was first
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now