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?

 

[GK039]

Array formulas do slow down the calculation speed anyway. Depending on the size of your workbook and the number of formulas used it is common for array formulas to slow down calculations

George

Hi, I am a relatively basic user, I have built a spreadsheet that consists of 4 worksheets:<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?

 

[Aladin Akyurek]

What are the formulas that you invoke?

 

[Lee75_UK]

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?

 

[shg]

Try this:

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

 

[T. Valko]

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?

For tips on efficiency:

http://www.decisionmodels.com/

 

[Lee75_UK]

Try this:

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

Hi,

I entered the following and it returned #NAME? as the error??

=SUMIFS('Master Data'!R2:R64998,'Master Data'!B2:B64998,'SKU & HLs'!B3,'Master Data'!Q2:Q64998,"JAN",'Master Data'!P2:P64998,"EXPORT")

 

[T. Valko]

Hi,

I entered the following and it returned #NAME? as the error??

=SUMIFS('Master Data'!R2:R64998,'Master Data'!B2:B64998,'SKU & HLs'!B3,'Master Data'!Q2:Q64998,"JAN",'Master Data'!P2:P64998,"EXPORT")

What version of Excel are you using?

The SUMIFS function requires that you be using Excel 2007 or later. It's not compatible with earlier versions.

 

[Lee75_UK]

What version of Excel are you using?

The SUMIFS function requires that you be using Excel 2007 or later. It's not compatible with earlier versions.

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

Any other suggestions?

Cheers

Lee

 

[shg]

That's unfortunate; as Tony says, SUMIFS requires Excel 2007+

How about a pivot table?