Array Formula
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Array Formula

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

    Default

     
    I use this array formula to filter and sum:
    {=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
    My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
    Thank you for you time
    David Stanley

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

    Default

    On 2002-03-13 07:02, DStan wrote:
    I use this array formula to filter and sum:
    {=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
    My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
    Thank you for you time
    David Stanley
    David,

    Are you using this formula in a single or multiple cells?

    Do you really have data that fills the range 1 to 50000?

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,333
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try

    =SUM(($A$2:$A$5=10)*($B$2:$B$5>=740)*($B$2:$B$5<=749)*($E$2:$E$5=I2)*($F$2:$F$5=I3)*($G$2:$G$5))

    I shortened your ranges to test.
    Revise the criteria cells as necessary.

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

    Default

    On 2002-03-13 07:40, Aladin Akyurek wrote:
    On 2002-03-13 07:02, DStan wrote:
    I use this array formula to filter and sum:
    {=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
    My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
    Thank you for you time
    David Stanley
    David,

    Are you using this formula in a single or multiple cells?

    Do you really have data that fills the range 1 to 50000?

    Aladin
    Aladin

    This is a single cell formula. I am importing data from access database and used large number because 5000 wasn't enough so I added a zero. Should I reduce this number?

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

    Default

      
    On 2002-03-13 12:15, DStan wrote:
    On 2002-03-13 07:40, Aladin Akyurek wrote:
    On 2002-03-13 07:02, DStan wrote:
    I use this array formula to filter and sum:
    {=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
    My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
    Thank you for you time
    David Stanley
    David,

    Are you using this formula in a single or multiple cells?

    Do you really have data that fills the range 1 to 50000?

    Aladin
    Aladin

    This is a single cell formula. I am importing data from access database and used large number because 5000 wasn't enough so I added a zero. Should I reduce this number?
    There is indeed no need to have the formula to look at unused cells. And, you have data that apparently changes over time. That calls for dynamically computing the ranges of interest.

    It looks like column G in BWP EXPENSE is of numeric type, which we can use for that purpose.

    May I suggest that you modify the name of the sheet to just BWPexpense (no space), although it doesn't matter at all how it's called to what follows.

    Activate Insert|Name|Define.
    Type "EndRow" in the Names in Workbook box.
    Enter as formula in the 'Refers To' box:

    =MATCH(9.99999999999999E+307,BWPexpense!$G:$G)

    Click OK.

    Activate the sheet where you want the formula.

    In E1 enter: 10
    In E2 enter: 740
    In E3 enter: 749
    In E4 enter: 1999
    In E5 enter: 7

    These are criteria/conditions that you appear to use.

    You can also use, as noted by Dave Patton, a normally entered SUMPRODUCT formula instead of an array formula, in which the definite references are replaced with dynamically computed ranges by using the OFFSET worksheet function:

    =SUMPRODUCT((OFFSET(BWPexpense!$A$2,0,0,EndRow-1,1)=E1)*(OFFSET(BWPexpense!$B$2,0,0,EndRow-1,1)>=E2)*(OFFSET(BWPexpense!$B$2,0,0,EndRow-1,1)<=E3)*(OFFSET(BWPexpense!$E$2,0,0,EndRow-1,1)=E4)*(OFFSET(BWPexpense!$F$2,0,0,EndRow-1,1)<=E5),OFFSET(BWPexpense!$G$2,0,0,EndRow-1,1))

    That should do it.

    You should not need to touch this formula whenever you choose a defferent set of criteria/conditions and/or the number of records change in the data area.

    Aladin

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