+ Reply to Thread
Results 1 to 3 of 3

How to change a number after every fifth cell

  1. #1
    Registered User
    Join Date
    05-16-2017
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    4

    How to change a number after every fifth cell

    Hi Guys,

    So i have to create a column of numbers, where every sixth number needs to take the number above it and get multiplied by a cell that is fixed. After that this number needs to continue to be the same for 5 cells and then follow the exact same procedure as before.

    So current formula looks something like this but now it wont automatically do that for the every sixth cell.

    1
    1
    1
    1
    1
    =$B$6*$F$2
    =$B$6*$F$2
    =$B$6*$F$2
    =$B$6*$F$2
    =$B$6*$F$2
    =$B$11*$F$2
    =$B$11*$F$2
    =$B$11*$F$2
    =$B$11*$F$2
    =$B$11*$F$2

    1
    1
    1
    1
    1
    5
    5
    5
    5
    5
    25
    25
    25
    25
    25

    In essence the theory is that the rent of a house increases every 5 years by 5% or 10% or whatever.
    I need to do this for a **** load of years but cannot figure out if there is any way i can do this automatically and not manually.
    Your help would be really appreciated.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,769

    Re: How to change a number after every fifth cell

    I find that a big part of spreadsheet programming is figuring out the right mix of relative and absolute referencing. I cannot be sure exactly what your references are. If I assume that your constant multiplier is F2, and column B is the column of interest, I would:

    1) Select B6:B10, type 1, ctrl-shift-enter to enter that 1 in all 5 cells
    2) In B11, enter =B6*$F$2
    3) copy B11, select B12:B15, paste. (B15 will be =B10*$F$2)
    4) select B16:B20, paste again.
    5) steps 3 and 4 illustrate how this mix of relative and absolute references works. Once you understand what is going on, then select the desired cells in column B and paste again. You should be done.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    03-05-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    301

    Re: How to change a number after every fifth cell

    This is a simple single formula that should help

    The following example uses the MOD function. In this example, you must begin on row five, because you cannot calculate rows that don't exist, since you're attempting to calculate the five rows above the formula.

    So the formula here will only make the calculation if it satisfies the criteria of being a MOD of 5. that is, on ever 5th row do the calculation.

    1. In your spreadsheet, go to Column A Row 5
    2. Type in the start number (e.g 1)
    3. In row 6 enter the formula
    4. Formula: copy to clipboard
      Please Login or Register  to view this content.
    5. Fill down

    You can change the MOD criteria as required, but hopefully this will give you a good starting idea.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to change number to number in another cell
    By Anarchus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-07-2017, 09:13 PM
  2. Replies: 10
    Last Post: 10-21-2015, 09:06 PM
  3. [SOLVED] Varying number in one cell, to change to a set number in another
    By Geoffo123 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-11-2012, 08:36 PM
  4. Change cell's number format based on another cell's value
    By Sharpshooter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-04-2010, 05:06 AM
  5. Replies: 7
    Last Post: 11-27-2008, 10:31 AM
  6. Replies: 2
    Last Post: 01-13-2006, 05:00 PM
  7. [SOLVED] change cell shading when a number differs from the previous number
    By zooeyhallne in forum Excel General
    Replies: 1
    Last Post: 06-06-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1