Sumproduct or some such Array Solution - Page 2
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Sumproduct or some such Array Solution

  1. #11
    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-08 03:34, Ian Mac wrote:
    Aladin,

    Your quite right INDIRECT() is not working with the SUMPRODUCT(), I was merely tryint to avoid having to run all the IF statements within every formula, I'm assuming there's no other way.

    not entirely sure what you mean by 'Original Form', but I have amended the formula to:

    =SUMPRODUCT((OFFSET(RAWDATA!$B$1,0,0,COUNTA(RAWDATA!$B:$B),1)=$B8)*(OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)=IF( LEN(Site)>1,Site,OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)))*(OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)=IF(LEN(Progr ammeName)>1,ProgrammeName,OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)))*(OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)=IF( LEN(LINEMANAGER)>1,LINEMANAGER,OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)))*(OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1)=IF(LE N(EMPTYPE2)>1,EMPTYPE2,OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1))),OFFSET(RAWDATA!$H$1,0,0,COUNTA(RAWDATA!$H:$H),1))

    which is what I think you mean.

    it is running a small ammount faster but not significantly enough to warrant the changes to the Original, BUT the fact that it replaces 16 IF's (or conditions rather) as opposed to the 9 conditions orginally, it does (nearly). Also the fact that the file is some 37368 cells lighter makes the the time concerns ALMOST worth it.
    Ian,

    This is indeed what I had mind. I have a small proposal though.

    Insert an additional row or two in RAWDATA.

    Enter in A1:

    =MATCH(9.99999999999999E+307,H:H)

    where I assume H to be numeric.

    Put the above formula in B1.

    Remove all the RAWDATA! bit.

    Replace all COUNTA's by $A$1.

    You can fetch the result of this formula by simply giving a name to B1 and using that name everywhere you need it.

    I'm curious it would make any noticeable difference this way in performance.

    Aladin

    I believe you shoud get rid off recomputing the same thing many times with COUNTA.


    [ This Message was edited by: Aladin Akyurek on 2002-03-08 05:42 ]

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

    Default

    On 2002-03-08 05:41, Aladin Akyurek wrote:
    On 2002-03-08 03:34, Ian Mac wrote:
    Aladin,

    Your quite right INDIRECT() is not working with the SUMPRODUCT(), I was merely tryint to avoid having to run all the IF statements within every formula, I'm assuming there's no other way.

    not entirely sure what you mean by 'Original Form', but I have amended the formula to:

    =SUMPRODUCT((OFFSET(RAWDATA!$B$1,0,0,COUNTA(RAWDATA!$B:$B),1)=$B8)*(OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)=IF( LEN(Site)>1,Site,OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)))*(OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)=IF(LEN(Progr ammeName)>1,ProgrammeName,OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)))*(OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)=IF( LEN(LINEMANAGER)>1,LINEMANAGER,OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)))*(OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1)=IF(LE N(EMPTYPE2)>1,EMPTYPE2,OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1))),OFFSET(RAWDATA!$H$1,0,0,COUNTA(RAWDATA!$H:$H),1))

    which is what I think you mean.

    it is running a small ammount faster but not significantly enough to warrant the changes to the Original, BUT the fact that it replaces 16 IF's (or conditions rather) as opposed to the 9 conditions orginally, it does (nearly). Also the fact that the file is some 37368 cells lighter makes the the time concerns ALMOST worth it.
    Ian,

    This is indeed what I had mind. I have a small proposal though.

    Insert an additional row or two in RAWDATA.

    Enter in A1:

    =MATCH(9.99999999999999E+307,H:H)

    where I assume H to be numeric.

    Put the above formula in B1.

    Remove all the RAWDATA! bit.

    Replace all COUNTA's by $A$1.

    You can fetch the result of this formula by simply giving a name to B1 and using that name everywhere you need it.

    I'm curious it would make any noticeable difference this way in performance.

    Aladin

    I believe you shoud get rid off recomputing the same thing many times with COUNTA.


    [ This Message was edited by: Aladin Akyurek on 2002-03-08 05:42 ]
    Not sure what you mean?

    >Put the above formula in B1.
    What above formula (mine?, the MATCH()?)

    >Remove all the RAWDATA! bit.
    The results need to be returned on a different sheet from RAWDATA!.

    >Replace all COUNTA's by $A$1.
    by this you mean replace the COUNTA(RAWDATA!$H:$H) bit?? but I've already taken the RAWDATA! bit out, how do I reference that sheet.
    do you mean 'with'? I have many col references not just H:H.



    Thanks (thought this might spark some interest, it's my weekly Aladin Challenge )


    [ This Message was edited by: Ian Mac on 2002-03-08 06:55 ]

  3. #13
    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

      
    > Not sure what you mean?

    > Put the above formula in B1.
    > What above formula (mine?, the MATCH()?)


    I meant the SUMPRODUCT formula.

    > Remove all the RAWDATA! bit.
    > The results need to be returned on a
    > different sheet from RAWDATA!.


    Yes, I knew that - rather suspected that. My initial proposal should have been more explicit regarding this organization of your workbook. See below the recap.

    > Replace all COUNTA's by $A$1.
    > by this you mean replace
    > the COUNTA(RAWDATA!$H:$H) bit??
    > but I've already taken > the RAWDATA! bit out,
    > how do I reference that sheet.

    > do you mean 'with'? I have many
    > col references not just H:H.


    Since the SUMPRODUCT formula will reside in RAWDATA, the sheet names will no longer be needed. Again see the recap.

    > thought this might spark some interest,
    > it's my weekly Aladin Challenge


    Hmmm

    RECAP on my initial proposal:

    In RAWDATA:

    Insert a few rows before the data.

    In A1 enter:

    =MATCH(9.99999999999999E+307,H:H)

    In B1 enter: the SUMPRODUCT formula, which we need to adapt to the set up I'm proposing.

    Do one of:
    1. In A2 to Ai enter the necessary simple formulas that reads off criteria/conditions from the sheet where they are selected/or determined (I'll refer to this sheet as the Query sheet in what follows).
    2. Give intelligible/appropriate names via the Name Box to the cells in the Query sheet.


    In the Query sheet, enter in an appropriate cell:

    =RAWDATA!B1

    in order to read off the result computed in RAWDATA. This B1 is the cell where we have the SUMPRODUCT formula.

    Now, the amendement to the SUMPRODUCT formula:

    =SUMPRODUCT((OFFSET($B$3,0,0,$A$1,1)=$B8)*(OFFSET($C$1,0,0,$A$1,1)=IF(LEN(Site)>1,Site,...)

    Replace the conditon $B8 either with the cell from the range A2:Ai or the name it is given via the Name Box. Carry out the same kind of changes with the rest of the formula.

    My proposal boils down to moving the computations to the sheet (RAWDATA) where data are. RAWDATA either reads off the criteria/conditions from the Query sheet and the Query sheet determines the criteria/conditions and reads the result from RAWDATA where the real computation occurs.

    Note. The proposal eliminates lots of COUNTA's, a volatile function.

    Regards,

    Aladin

    Addendum: Many edits have to do with my attempt to cope/learn a bit the behavior of BBcode.

    On 2002-03-08 06:54, Ian Mac wrote:
    On 2002-03-08 05:41, Aladin Akyurek wrote:
    On 2002-03-08 03:34, Ian Mac wrote:
    Aladin,

    Your quite right INDIRECT() is not working with the SUMPRODUCT(), I was merely tryint to avoid having to run all the IF statements within every formula, I'm assuming there's no other way.

    not entirely sure what you mean by 'Original Form', but I have amended the formula to:

    =SUMPRODUCT((OFFSET(RAWDATA!$B$1,0,0,COUNTA(RAWDATA!$B:$B),1)=$B8)*(OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)=IF( LEN(Site)>1,Site,OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)))*(OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)=IF(LEN(Progr ammeName)>1,ProgrammeName,OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)))*(OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)=IF( LEN(LINEMANAGER)>1,LINEMANAGER,OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)))*(OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1)=IF(LE N(EMPTYPE2)>1,EMPTYPE2,OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1))),OFFSET(RAWDATA!$H$1,0,0,COUNTA(RAWDATA!$H:$H),1))

    which is what I think you mean.

    it is running a small ammount faster but not significantly enough to warrant the changes to the Original, BUT the fact that it replaces 16 IF's (or conditions rather) as opposed to the 9 conditions orginally, it does (nearly). Also the fact that the file is some 37368 cells lighter makes the the time concerns ALMOST worth it.
    Ian,

    This is indeed what I had mind. I have a small proposal though.

    Insert an additional row or two in RAWDATA.

    Enter in A1:

    =MATCH(9.99999999999999E+307,H:H)

    where I assume H to be numeric.

    Put the above formula in B1.

    Remove all the RAWDATA! bit.

    Replace all COUNTA's by $A$1.

    You can fetch the result of this formula by simply giving a name to B1 and using that name everywhere you need it.

    I'm curious it would make any noticeable difference this way in performance.

    Aladin

    I believe you shoud get rid off recomputing the same thing many times with COUNTA.


    [ This Message was edited by: Aladin Akyurek on 2002-03-08 05:42 ]
    Not sure what you mean?

    >Put the above formula in B1.
    What above formula (mine?, the MATCH()?)

    >Remove all the RAWDATA! bit.
    The results need to be returned on a different sheet from RAWDATA!.

    >Replace all COUNTA's by $A$1.
    by this you mean replace the COUNTA(RAWDATA!$H:$H) bit?? but I've already taken the RAWDATA! bit out, how do I reference that sheet.
    do you mean 'with'? I have many col references not just H:H.



    Thanks (thought this might spark some interest, it's my weekly Aladin Challenge )


    [ This Message was edited by: Ian Mac on 2002-03-08 06:55 ]
    [ This Message was edited by: Aladin Akyurek on 2002-03-09 11:41 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 11:43 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 11:48 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 11:50 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 11:55 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-09 13:55 ]

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