+ Reply to Thread
Results 1 to 8 of 8

Dynamic MOD & COLUMNS formula

  1. #1
    Registered User
    Join Date
    08-19-2019
    Location
    Clarksville, TN
    MS-Off Ver
    2010
    Posts
    5

    Dynamic MOD & COLUMNS formula

    I'm trying to make a formula dynamic. I have data rows by date. I have a formula that will look at every 7'th column so that I can average, for example, performance on Mondays.

    Here is my formula:
    =AVERAGE(IF((DataInput!$F$41:$GJ$43<>"")*(MOD(COLUMN(DataInput!$F$41:$GJ$43),F3)=F2),DataInput!$F$41:$GJ$43))

    What I'd like to do is adjust the beginning and end dates specified by the "DataInput!$F$41:$GJ$43" portions of the formula.

    I've entered a data "List" and can look up the column using VLookup function. But I'm having some difficulty putting it all together. Any ideas? Your help would be greatly appreciated!

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

    Re: Dynamic MOD & COLUMNS formula

    You said data rows by date, but F41:GJ43 is only three rows by 187 columns. Are the dates actually in the columns F41:GJ41? Are they DAILY DATES?
    Are you trying to perform the average on the lower two rows?


    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-19-2019
    Location
    Clarksville, TN
    MS-Off Ver
    2010
    Posts
    5

    Re: Dynamic MOD & COLUMNS formula

    OK, an example of the formula is in cell F5 of the Analysis sheet.

    That particular cell is averaging data from three rows on the DataInput sheet.

    The input is done in the top half of DataInput sheet and consolidated in rows 40 - 55.
    Attached Files Attached Files
    Last edited by dcwood57; 08-19-2019 at 08:38 PM. Reason: rows not columns

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dynamic MOD & COLUMNS formula

    Currently unable to open excel files (at work), but take a look at using averageifs() for this. You can put the start/end dates intheir own cells and reference them, if needed.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    08-19-2019
    Location
    Clarksville, TN
    MS-Off Ver
    2010
    Posts
    5

    Re: Dynamic MOD & COLUMNS formula

    Not sure how averageifs() would help in this instance. Looking for the 7'th item in a row repeatedly so that I can, for example, see performance on Mondays.

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

    Re: Dynamic MOD & COLUMNS formula

    BeginDate =Analysis!$C$17
    DateRange1 =OFFSET(DataInput!$A$1,40,0,ROWS(DataInput!$A$41:$A$55),Analysis!$C$18-Analysis!$C$17+1)
    EndDate =Analysis!$C$18

    Analysis F5, (option one: all days within the specified range):
    Please Login or Register  to view this content.
    Alternatively F31, (option two:only the weekdays same as the beginning date
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 08-21-2019 at 12:18 AM.

  7. #7
    Registered User
    Join Date
    08-19-2019
    Location
    Clarksville, TN
    MS-Off Ver
    2010
    Posts
    5

    Re: Dynamic MOD & COLUMNS formula

    I sincerely appreciate the effort. However, the data coming back on this is dramatically different than what is coming from the original spreadsheet when I choose a date range that includes all dates. I'll spend some time trying to decipher exactly what your formulas do, and thanks for the effort, but I may just have to manually adjust the formulas in my present sheet to get what I'm looking for. I can probably do it with search & replace. Not very elegant, but feasible.

  8. #8
    Registered User
    Join Date
    08-19-2019
    Location
    Clarksville, TN
    MS-Off Ver
    2010
    Posts
    5

    Re: Dynamic MOD & COLUMNS formula

    INDIRECT function did the trick.
    {=AVERAGE(IF((INDIRECT($R13)<>"")*(MOD(COLUMN(INDIRECT($R13)),F$3)=F$2),INDIRECT($R13)))}

    Where R13 combined the first half of the range name with the second half which represented the date. Manually entering the range names, but way better than it was.

+ 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] Dynamic Unique List from multiple dynamic columns
    By JO505 in forum Excel General
    Replies: 7
    Last Post: 06-11-2015, 05:41 PM
  2. [SOLVED] sum() and average() formula with dynamic columns
    By umbata in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-25-2015, 12:29 PM
  3. Summarizing multiple dynamic columns with a formula
    By YellowOnline in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-30-2015, 07:59 AM
  4. [SOLVED] dynamic index match formula to transpose values across rows and then down columns
    By Bananas212 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-30-2014, 03:07 PM
  5. Formula array summing a dynamic range of columns
    By UMBiii in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2014, 05:07 PM
  6. Help Need Formula Array to sum dynamic range of columns VBA Macro
    By UMBiii in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 02-04-2014, 08:05 PM
  7. Dynamic Count If Formula with additional columns and rows.
    By nwd9s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2011, 09:12 PM

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