Sumproduct or some such Array Solution

[Ian Mac]

I have the following formula:

=IF(SiteName<>"",IF(ProgrammeName<>"",IF(LINEMANAGER<>"",IF(EMPTYPE2<>"",SUMIF(RAWDATA!$N:$N,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$M:$M,$E8,RAWDATA!$G:$G)),IF(EMPTYPE2<>"",SUMIF(RAWDATA!$K:$K,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$J:$J,$E8,RAWDATA!$G:$G))),IF(LINEMANAGER<>"",IF(EMPTYPE2<>"",SUMIF(RAWDATA!$P:$P,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$O:$O,$E8,RAWDATA!$G:$G)),IF(EMPTYPE2<>"",SUMIF(RAWDATA!$L:$L,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$I:$I,$E8,RAWDATA!$G:$G)))),SUMIF(RAWDATA!$B:$B,'EMS T&A REPORT'!$E8,RAWDATA!$G:$G))

Handed to me by someone else, I can go into more detail but on first inspection does any thing feel this would be easier acheived using an array.

what I can explain is:

the IF()'s are testing to see if there is any thing in the cells tested (fairly obivous).
What I'd like to know if you can test each say:

sumproduct(({SiteName,ProgrammeName,LINEMANAGER,EMPTYPE2}..............
and using TheAnswer with:

............indirect(Vlookup(TheAnswerSomethingIDon'tKnow?,{Answer1(all blank),"RawData!N:N";Answer2(AllBlankExceptSiteName),"RawData!M:M" Etc..........

With ............*(RawData!G:G).

one thing is I will be changing the N:N Etc. to dynamic named ranges (Can I do this).


Also, I tried this for just one of the IF()'s:

=SUMPRODUCT((RAWDATA!$N$2:$N$4671=E8)*(RAWDATA!$G$2:$G$4671))

but it doesn't work , what I will point is that E8 is a Text String.

I don't expect any answers now, as I don't fully understand it myself, but if anyone has any starters for 10 I'd be very great full, it's late 22:40 UK and I've been working since 08:30.



_________________
Kind Regards
Ian Mac
This message was edited by Ian Mac on 2002-03-05 14:22

 

[Aladin Akyurek]

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(ProgrammeName)>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(LEN(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

 

[Ian Mac]

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(ProgrammeName)>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(LEN(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

 

[Aladin Akyurek]

> 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:

• 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).
• 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(ProgrammeName)>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(LEN(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