series fill with same number twice

Posted on 2011-05-04
Last Modified: 2012-05-11
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)
Question by:howcheat
    LVL 12

    Accepted Solution

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

    Copy A3 down.

    LVL 12

    Expert Comment

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

    LVL 50

    Expert Comment


    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
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    You can change Alan3285's formula




    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

    if starting in row 7 then


    Author Closing Comment

    Great resolutions - All!! Alan3285 was first

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now