+ Reply to Thread
Results 1 to 3 of 3

Extend formula - for a range of columns

Hybrid View

  1. #1
    Registered User
    Join Date
    05-25-2020
    Location
    Arica, Chile
    MS-Off Ver
    365
    Posts
    1

    Extend formula - for a range of columns

    Hello,

    I am dealing with a problem and I just can't figure out what to do.
    I want to calculate an average of time I need for a task in a bunch of projects (=Hilftabelle!G16-G24).

    I have the following formula, which just works fine (as a example for the first three rows):

    Formula: copy to clipboard
    =(1/3)*(IF(OR(G2=Hilftabelle!G16;G2=Hilftabelle!G17;G2=Hilftabelle!G18;G2=Hilftabelle!G19;G2=Hilftabelle!G20;G2=Hilftabelle!G21;G2=Hilftabelle!G22;G2=Hilftabelle!G23;G2=Hilftabelle!G24);E2/F2)
    +IF(OR(G3=Hilftabelle!G16;G3=Hilftabelle!G17;G3=Hilftabelle!G18;G3=Hilftabelle!G19;G3=Hilftabelle!G20;G3=Hilftabelle!G21;G3=Hilftabelle!G22;G3=Hilftabelle!G23;G3=Hilftabelle!G24);E3/F3)
    +IF(OR(G4=Hilftabelle!G16;G4=Hilftabelle!G17;G4=Hilftabelle!G18;G4=Hilftabelle!G19;G4=Hilftabelle!G20;G4=Hilftabelle!G21;G4=Hilftabelle!G22;G4=Hilftabelle!G23;G4=Hilftabelle!G24);E4/F4)
    +...


    So, the IF/OR function checks if in the cell G2 (G3, G4) is a certain value (Hilftabelle!G16-24), namely one of the projects I examine. Of course there (in cell G2, G3 etc.) could be the name of other projects, which i do not want to include. Afterwards it devides the time (E2-E4, for example 1:00:00) with the number of task (F2-F4, for example 2).
    For example:

    Hilftabelle!G16= ProjectA
    E2 = 1:00:00 (Time spend for the Project)
    F2 = 2 (Tasks done in that time)
    Result = 0:30:00 (average time for a task).

    NOW MY PROBLEM:
    I want to extend this formula for a range from G2 (&E2,F2) just untill G999 (&G999,G999).
    Of course i could just copy and paste the formular for 999 times, but this is not really a time-saving method.

    What is important: When there are no values or values other than Hilftabelle!G16-24 (which is the values for the projects i want to consider) the formula should not include them.

    I hope the problem gets clear,
    Thank you very much in advance!

    Wabisch
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Extend formula - for a range of columns

    Firstly you can simplify your formula to =(1/3)*(IF(ISNA(MATCH(G2,Hilftabelle!G16:G31,0)),0,E2/F2)+IF(ISNA(MATCH(G3,Hilftabelle!G16:G31,0)),0,E3/F3))
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,960

    Re: Extend formula - for a range of columns

    use a (hidden) helper column (k) with:
    IFERROR(E2/F2,0)
    Then calculate average time per task with:
    =AVERAGEIF(K2:INDEX(K:K,COUNT(K:K)),"<>0")
    Attached Files Attached Files
    Ben Van Johnson

+ 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] automatically extend columns while keeping a minimum width on specific columns
    By enhydra in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2017, 10:13 PM
  2. Extend formula range dinamically while using an array ?
    By andreapenna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2017, 10:48 AM
  3. How can I extend the range of formula for multiple cells quickly
    By taylorsm in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-03-2017, 01:24 PM
  4. [SOLVED] VBA Extend Formula To Multiple Columns
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-01-2015, 09:48 AM
  5. Formula range auto extend doesn't work in office 2007
    By crunchor in forum Excel General
    Replies: 3
    Last Post: 08-03-2014, 09:56 PM
  6. [SOLVED] Extend current formula for a date range
    By playaller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2013, 06:26 PM
  7. Extend Number of columns
    By hme in forum Excel General
    Replies: 5
    Last Post: 04-29-2006, 02:16 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