+ Reply to Thread
Results 1 to 6 of 6

Using the value from one cell to act as reference for a series of cells in an average fnct

  1. 08-11-2015, 02:09 PM #1
    jreil563
    jreil563 is offline
    Registered User
    Join Date
    08-11-2015
    Location
    London, Canada
    MS-Off Ver
    2010
    Posts
    5

    Using the value from one cell to act as reference for a series of cells in an average fnct

    Hello, I am looking for some help in completing a rather complicated function in excel. I believe I have the basic idea together, however i can't figure out how to make the multiple components of the function work together. I am trying to use the value of one cell to pull through to another cell's formula as the cell reference for the series of cells I want to average together. =AVERAGE((G(D1):G(D2))) is the formula I have right now where G is the series that I want to average and the D cells having the range of cells in G that I want. For example, If D1 is 8 and D2 is 12, then I want the final formula to be the average of G8:G12 Any help you can provide is greatly appreciated.
    Reply With QuoteRegister To Reply
  2. 08-11-2015, 02:18 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,671

    Re: Using the value from one cell to act as reference for a series of cells in an average

    try ...

    =AVERAGE(INDIRECT("G" & D1 & ":G" & D2))
    Reply With QuoteRegister To Reply
  3. 08-11-2015, 02:19 PM #3
    FDibbins
    FDibbins is offline
    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
    53,044

    Re: Using the value from one cell to act as reference for a series of cells in an average

    Hi, welcome to the forum

    perhaps this...

    =AVERAGE(indirect("G"&D1&":G"&D2)
    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
    Reply With QuoteRegister To Reply
  4. 08-11-2015, 02:34 PM #4
    jreil563
    jreil563 is offline
    Registered User
    Join Date
    08-11-2015
    Location
    London, Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: Using the value from one cell to act as reference for a series of cells in an average

    Thank you! This is so helpful. If the G column data was located on another sheet how would I change your formula to accommodate? if possible
    Reply With QuoteRegister To Reply
  5. 08-11-2015, 03:25 PM #5
    FDibbins
    FDibbins is offline
    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
    53,044

    Re: Using the value from one cell to act as reference for a series of cells in an average

    try this...
    =AVERAGE(indirect("'sheetname'!G"&D1&":'sheetname'!G"&D2)
    Reply With QuoteRegister To Reply
  6. 08-11-2015, 04:45 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using the value from one cell to act as reference for a series of cells in an average

    Here's another one...

    =AVERAGE(INDEX(G:G,D1):INDEX(G:G,D2))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Series Name Reference Cell
    By strother1990 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-19-2015, 07:10 AM
  2. [SOLVED] average if, reference to other cells
    By mkeys4 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-24-2014, 04:38 PM
  3. help with average of a series of data (advanced) column reference?
    By mysticmoron109 in forum Excel General
    Replies: 11
    Last Post: 08-14-2009, 11:53 AM
  4. how do i average a series of cells consecutively
    By ajoros in forum Excel General
    Replies: 3
    Last Post: 06-16-2009, 04:04 PM
  5. Average of last 5 cells in a series.
    By bumpo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2008, 06:22 PM
  6. I cant get Excel OFFSET Fnct to return multiple references.
    By dmkirk in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-15-2005, 09:05 AM
  7. How can I autofill a series to reference non adjacent cells?
    By Microcell in forum Excel General
    Replies: 1
    Last Post: 06-30-2005, 05: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1