Referencing a complete column
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Referencing a complete column

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I use a 2 sheet workbook. The first sheet has a list of sales figures associated with salesmen. On the 2nd sheet I have a summary of the sales - total, average order value etc. I use a simple formula

    =SUMIF('Orders Received'F1:F300,"=Accepted",'Orders Received'!C1:C300)

    That works fine on a regional basis but on a national basis the sales could very in number form 1000 to 20,000. How can I alter the formula so that it looks at the whole of columns C and F?

    All help gratefully received.

    Dave

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    C:C and F:F should do it.

    However, this will look at all 65,000 cells in both cases and may slow down the calc

    Have a look at dynamic ranges : Dave has some good examples at Ozgrid

    http://www.ozgrid.com/Excel/DynamicRanges.htm




    _________________
    Hope this helps,
    Chris


    [ This Message was edited by: Chris Davison on 2002-03-17 06:44 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,864
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-17 06:21, daveray wrote:
    I use a 2 sheet workbook. The first sheet has a list of sales figures associated with salesmen. On the 2nd sheet I have a summary of the sales - total, average order value etc. I use a simple formula

    =SUMIF('Orders Received'F1:F300,"=Accepted",'Orders Received'!C1:C300)

    That works fine on a regional basis but on a national basis the sales could very in number form 1000 to 20,000. How can I alter the formula so that it looks at the whole of columns C and F?

    All help gratefully received.

    Dave
    Dave,

    There are 2 methods you can use to cope with the ever changing data area.

    [1] Compute dynamically the relevant ranges in the summary sheet from within the SUMIF formulas that you use, or
    [2] Name the dynamically computed relevant ranges via Insert|Name|Define.

    Method 2 makes the named ranges in all worksheets of your WB, while Method 1 keeps them local to the summary sheet.

    Lets take Method 1 first.

    In say A1 in the summary sheet enter:

    =MATCH(9.99999999999999E+307,'Orders Received'!C:C)

    Note 1. MATCH is fast enough and looks just at one column, so you don't have to worry about that.

    Now you can (re)write your example SUMIF formula as:

    =SUMIF(OFFSET('Orders Received'F1,0,0,$A$1,1),"Accepted",OFFSET('Orders Received'!C1,0,0,$A$1,1))

    A corresponding AVERAGE formula would be:

    =SUMIF(OFFSET('Orders Received'F1,0,0,$A$1,1),"Accepted",OFFSET('Orders Received'!C1,0,0,$A$1,1))/MAX(1,COUNTIF(OFFSET('Orders Received'F1,0,0,$A$1,1),"Accepted"))

    Method 2.

    Activate the option Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,'Orders Received'!C:C)

    Click Add.

    Enter as name, e.g., Orders in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET('Orders Received'$F$1,0,0,EndRow,1)

    Note that this defines a dynamic range as one-column range (that is, a dynamic range in F of Orders Received).

    Click Add.

    Enter as name, e.g., Amounts in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET('Orders Received'$C$1,0,0,EndRow,1)

    Click OK.

    Note 2. You need for every relevant subarea of data area to set up a name and a formula.

    After having created named dynamic ranges, you can (re)write your example SUMIF formula as:

    =SUMIF(Orders,"Accepted",Amounts)

    A corresponding AVERAGE formula would be:

    =SUMIF(Orders,"Accepted",Amounts))/MAX(1,COUNTIF(Orders,"Accepted"))

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-03-17 08:47 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com