Formula nighmare !!

[Lee75_UK]

Hi, I am a relatively basic user, I have built a spreadsheet that consists of 4 worksheets:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sheet 1: I enter raw data.<o></o>
<o></o>
Sheets 2, 3 & 4 pulls information from the raw data using predominantly array formulas.<o></o>
<o></o>
Due to the number of formulas, I am struggling to open, update and then save the results.<o></o>
<o></o>
I have 3 areas I need help with:<o></o>
<o></o>
1, The spreadsheet has tripled in size over night. What are the possible causes?<o></o>
<o></o>
2, When saving the spreadsheet, it seems to stall @ 61% and takes over 10-15 minutes to save. What are the possible causes?
<o></o>
3, To try and validate that all the data has pulled through correctly, I have a number of lookup formulas between each worksheet. I can only assume that this will also be slowing down the sheet, can anyone suggest a better solution?

 

[T. Valko]

I'm using 2003 :0( and its work related so can't upgrade !!

Any other suggestions?

Cheers

Lee

Check that link I posted!

 

[snoopyhr]

Maybe you want to give this a try, still array but drops all the ifs.. hope it works for you...

=SUM(('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8)*('Master Data'!$Q$2:$Q$64998="APR")*('Master Data'!$P$2:$P$64998="EXPORT")*('Master Data'!$R$2:$R$64998))

 

[T. Valko]

Maybe you want to give this a try, still array but drops all the ifs.. hope it works for you...

=SUM(('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8)*('Master Data'!$Q$2:$Q$64998="APR")*('Master Data'!$P$2:$P$64998="EXPORT")*('Master Data'!$R$2:$R$64998))

The IFs are slghtly more efficient over a large range compared to array multiplication.

 

[Ron Coderre]

I *may* have found a solution AND an actual use for DSUM!

With this set up:
Master Data sheet
B1: Name...or whatever column heading you used
B2:B64998 contains the values...Including the value of 'SKU & HLs'!B8

P1: ImpExp
P2:P64998 contains the values...including "Export"

Q1: Month
Q2:Q64998 contains the values...including Apr

R1: Amount
R2:R64998 contains the values to be added

AND...
V1: Name
V2: ='SKU & HLs'!B8

W1: ImpExp
W2: Export

X1: Month
X2: Apr

Now...this regular formula sums the Col_R values where the corresponding
Col_B value matches: 'SKU & HLs'!B8
and
Col_P value matches: Export
and
Col_Q value matches: Apr

T1: =DSUM(B1:R64998,R1,V1:X2)

It calculates fast and seems to have very little overhead.

Something you can use?

 

[Aladin Akyurek]

Thanks for the quick response.

Below is an example of the array formula i use. I am basically asking it to validate 3 different conditions, if all are matched then display the results.

=SUM(IF('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8,IF('Master Data'!$Q$2:$Q$64998="APR",IF('Master Data'!$P$2:$P$64998="EXPORT",'Master Data'!$R$2:$R$64998))))

Is there any alternatives to array formuals?

That's well-formed. A direct alternative for multi-conditional calculations are database functions like DSUM, DCOUNT, etc. On Excel 2007 or later, such formulas can be often re-expressed in terms of SUMIFS, COUNTIFS, etc.

Another option regarding this case is...

a) Restrict the references the formula is designed to process to the used range by means of dynamic named ranges. Also worth looking at the following UDF...

_________________________________

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function
_________________________________

The formula above would become with USED something like...

=SUM(
   IF(USED('Master Data'!$B:$B$)='SKU & HLs'!$B8,
   IF(USED('Master Data'!$Q:$Q)="APR",
   IF(USED('Master Data'!$P:$P)="EXPORT",
     USED('Master Data'!$R:$R)))))

b) If the ranges referred in the formula are referenced in many such formulas, all involving equality tests, you may want to switch to formulas with lesser conditions or to single condition SUMIF formulas. This requires judicious use of concatenation:

Z2 and downwards:

=B2&"|"&Q2&"|"&P2

Note that this method increases the number of formulas in the workbook, while allowing for simpler "multi-conditional" calcs...

The formula under consideration would become...

=SUMIF(
   'Master Data'!$Z$2:$Z$64998,
     'SKU & HLs'!$B8&"|"&"APR"&"|"&"EXPORT",
   'Master Data'!$R$2:$R$64998)

Or even...

=SUMIF(
    USED('Master Data'!$Z:$Z),
       'SKU & HLs'!$B8&"|"&"APR"&"|"&"EXPORT",
    USED('Master Data'!$R:$R))

Performance improvements can sometimes be dramatic:

http://www.mrexcel.com/forum/showthread.php?t=59969


Do you have also lots formulas with so-called volatile functions like OFFSET, INDIRECT, lots of look up formulas with match-type set to 0/FALSE?

By the way, have also a look at: http://www.decisionmodels.com/ for some more ideas.

 

[Ron Coderre]

Doesn't that seem like an awful lot of work to accomplish the same thing that DSUM does?...or am I missing something?

 

[T. Valko]

Doesn't that seem like an awful lot of work to accomplish the same thing that DSUM does?...or am I missing something?

So, I'm not the only one he ignores?

 

[Lee75_UK]

Guys, like I have explained I am quite a novice to excel and I’m trying to teach myself as I go.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I will go through each of the solutions that have been supplied and let you know how I get on.<o></o>
<o></o>
I really appreciate the support.<o></o>
<o></o>
One last question, I tried to copy & paste (special) the values of my master data (raw data) into a new workbook, but when I clicked on paste special it looked like it was trying paste in a picture instead of data. What are the possible causes?

<o></o>

 

[Lee75_UK]

The IFs are slghtly more efficient over a large range compared to array multiplication.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
The array formula I am using currently returns a value of 1796 which is correct. When i put in the above array multiplication it returns a zero value <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 12pt; HEIGHT: 15.75pt" id=_x0000_i1025 alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/confused.gif" src="file:///C:\DOCUME~1\LEE~1.BLA\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>
<o></o>
I may not have been clear to what I am trying to achieve. My array formula is:<o></o>
<o></o>
{=SUM(IF('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8,IF('Master Data'!$Q$2:$Q$64998="APR",IF('Master Data'!$P$2:$P$64998="EXPORT",'Master Data'!$R$2:$R$64998))))}<o></o>
<o></o>
'Master Data' Column B = Product code<o></o>
'SKU & HLs' Column B also = Product code<o></o>
<o></o>
1st condition is: The product code in worksheet Master Data column B must match the product code in worksheet SKU & HLs column B.<o></o>
<o></o>
'Master Data' Column Q = Period<o></o>
<o></o>
2nd condition is: The period must = APR<o></o>
<o></o>
'Master Data' Column P = Type of movement<o></o>
<o></o>
3rd condition is: The movement type must = Export<o></o>
<o></o>
'Master Data' Column R = Number of packages.<o></o>
<o></o>
If the 3 conditions are met then I need it to calculate column R<o></o>
<o></o>
Apologies if I sound patronising, but it’s the only way I can get my point across.<o></o>

 

[T. Valko]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
The array formula I am using currently returns a value of 1796 which is correct. When i put in the above array multiplication it returns a zero value <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></vath><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 15.75pt" type="#_x0000_t75" alt="0"><v:imagedata src="file:///C:\DOCUME~1\LEE~1.BLA\LOCALS~1\Temp\msohtml1\01\clip_image001.gif" o:href="http://www.mrexcel.com/forum/images/smilies/confused.gif"></v:imagedata></v:shape><o></o>
<o></o>
I may not have been clear to what I am trying to achieve. My array formula is:<o></o>
<o></o>
{=SUM(IF('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8,IF('Master Data'!$Q$2:$Q$64998="APR",IF('Master Data'!$P$2:$P$64998="EXPORT",'Master Data'!$R$2:$R$64998))))}<o></o>
<o></o>
'Master Data' Column B = Product code<o></o>
'SKU & HLs' Column B also = Product code<o></o>
<o></o>
1st condition is: The product code in worksheet Master Data column B must match the product code in worksheet SKU & HLs column B.<o></o>
<o></o>
'Master Data' Column Q = Period<o></o>
<o></o>
2nd condition is: The period must = APR<o></o>
<o></o>
'Master Data' Column P = Type of movement<o></o>
<o></o>
3rd condition is: The movement type must = Export<o></o>
<o></o>
'Master Data' Column R = Number of packages.<o></o>
<o></o>
If the 3 conditions are met then I need it to calculate column R<o></o>
<o></o>
Apologies if I sound patronising, but it’s the only way I can get my point across.<o></o>

What I'm saying is...

This formula syntax:

=SUM(IF('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8,IF('Master Data'!$Q$2:$Q$64998="APR",IF('Master Data'!$P$2:$P$64998="EXPORT",'Master Data'!$R$2:$R$64998))))

Is slighlty more efficient over large ranges than this formula syntax:

=SUM(('Master Data'!$B$2:$B$64998='SKU & HLs'!$B8)*('Master Data'!$Q$2:$Q$64998="APR")*('Master Data'!$P$2:$P$64998="EXPORT")*('Master Data'!$R$2:$R$64998))