+ Reply to Thread
Results 1 to 5 of 5

Help Copying an Array Formula down an Entire Column

  1. #1
    Registered User
    Join Date
    02-02-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Help Copying an Array Formula down an Entire Column

    Hi there - I'm a very beginner-intermediate Excel user as far as formulas go - I don't know most of the technical verbiage, nor what most of the formula handles (that's what they're called, right?) even mean, let alone what they do. I can basically figure things out as I go by finding examples on different forums, etc, but I've run across a seemingly very simple problem that for the life of me I can't figure out.

    I have an array formula that I need to copy down the entire column. The formula is: =SUM(IF(IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1)>0,IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1),0)*W2:W4)

    I need the Row values to change, not the "array formula parts" (because I don't know what they really are called). So, as I copy down I need the new formula to read: =SUM(IF(IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1)>0,IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1),0)*W2:W4)

    The only way I've been able to do that is to rewrite the whole formula in the new row cell. Please tell me there's a better way!

  2. #2
    Registered User
    Join Date
    02-21-2011
    Location
    Australia
    MS-Off Ver
    MS Office 2016 on Windows 10
    Posts
    42

    Re: Help Copying an Array Formula down an Entire Column

    Second formula adjusted: Give this a try.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Help Copying an Array Formula down an Entire Column

    the $ usually fixes a cell or range

    you have
    =SUM(IF(IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1)>0,IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1),0)*W2:W4)
    going down one row - but the K$2 changes to K$4
    =SUM(IF(IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1)>0,IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1),0)*W2:W4)

    do you want that to happen

    to fix a row us the $
    so if you want
    V3:V5,V3:V5-V2:V4
    NOT to change as you copy down the rows use
    V$3:V$5,V$3:V$5-V$2:V$4
    that fixes the row

    if you want it to change as you copy down then leave the $ off
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    02-02-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Help Copying an Array Formula down an Entire Column

    Hooray!! That worked - thank you SO much!! I knew it had to be something super simple

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Help Copying an Array Formula down an Entire Column

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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] PERCENTRANK Entire Column via Array Formula
    By Chip4Pips in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-28-2013, 02:33 PM
  2. Copying the same value into an entire column
    By seigna in forum Excel General
    Replies: 2
    Last Post: 05-11-2013, 02:53 PM
  3. Replies: 1
    Last Post: 05-09-2012, 05:30 PM
  4. copying an entire column
    By dlb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-16-2006, 11:34 AM
  5. Replies: 3
    Last Post: 04-20-2005, 11:51 AM

Tags for this Thread

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