Can a spreadsheet be too complicated for the computer to run

[Rachie1987]

Basically i have a huge spreadsheet, its almost 6000KB and my rubbish
com,puter cannot cope with it.

I have an intel pentium 4 with 3.06GHz, 1.9GB hard drive according to the
system properties.

My spreadsheet has the following formula repeated on numerous lines:


=IF(ISERROR(VLOOKUP($A15,ITLD004!$A:$E,4,FALSE)),0,VLOOKUP($A15,ITLD004!$A:$E,4,FALSE))

IT are saying its not my computer, i have over complicated the spreadsheet.
Bearing in mind their first exuse for it crashing was that the columns were
not all alligned correctly and this was causing it to crash :| i do not
believe what they are saying. Surely there are larger companies using excel
to a larger scale.

However i am not 100~% sure on the computer side of things so if anyone out
there can help it really would be much appreciated.

Can a spreadsheet this size be too complicated to run?

 

[Rachie1987]

Oh and i am using excel 2003 if this helps

 

[Mike H]

hi,

I know little about computers but your processor seems fine. The hard disk
looks very small at 1.9gb, are you sure about that.

Looking at the formula you posted, you have used full columns for the
vlookup and this will greatly increase calculation time, Could you cut down
the range being used in this formula for example I have altered it to 1000
lines, is this enough?

=IF(ISERROR(VLOOKUP($A15,ITLD004!$A1:$E1000,4,FALSE)),0,VLOOKUP($A15,ITLD004!$A1:$E1000,4,FALSE))

Also in the vlookup your referencing column E but returning column D, why
not take a column out?

=IF(ISERROR(VLOOKUP($A15,ITLD004!$A1:$D1000,4,FALSE)),0,VLOOKUP($A15,ITLD004!$A1:$D1000,4,FALSE))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rachie1987]

Hi Mike

Yes thats what the spec says in the system properties.
The formula has been copied across the page which searches different tabs
which all have different line lenghts which is why it is just the columns as
it was easier for me when copying the formulas along to make sure i had all
items listed rather than checking each tab. Same with only looking up D,
there are two sections on my spreadsheet and one looks up column E and one
looks up D, so i just copied and changed the column number - didnt realise
this affected it that much to cause it to crash!

Will change these and see if that helps!

Thanks

 

[Pete_UK]

Another approach that will speed things up a bit:

=IF(COUNTIF(ITLD004!$A:$A,$A15)=0,0,VLOOKUP($A15,ITLD004!$A:­$E,4,0))

This avoids doing the lookup twice, and also avoids the ISERROR call,
but has the same effect.

Hope this helps.

Pete

 

[Kenneth Andersen]

If you have acces to a computer with Excel 2007, then try it there. Excel
2007 is more powerfull - I have meassured calculation times being four times
longer on Excel 2003 than Excel 2007.
Both try in Excel 2003 format (*.xls) and try converting the file to Excel
2007 format (*.xlsx).

 

[Don Guillett]

I would start by changing your formula to ONLY look in the range needed. Get
rid of unneeded rows/columns.Do ctrl end to see what I mean

$A15,ITLD004!$A:$E,4,FALSE

$A15,ITLD004!$A2:$E300,4,0

 

[Rachie1987]

I have gotten rid of all the extra columns etc as Mike said at first, but it
still freezes up for about 20 minutes when i change something or try and
filter out one set, and half the time it doesnt even come back at all :|

I do not have access to 2007 at work but i tried it on one of our CAD
computers with dual core processors and as a comparrison timed a filter, what
took 7 minutes and 37 seconds on my computer took less than a second on this
one!

Definatly think my RAM needs to be updated

 

[Pete_UK]

You could also fix the values of the formulae which will not change,
and this will improve performance.

Hope this helps.

Pete

 

[Don Guillett]

And, did you adjust your formulas

 

[Jim Thomlinson]

What you have posted is not exactly the same. It is actually a lot better.
IsError is (IMO) a bad choice for that formula. If cells are deleted the
formula will return blank when in actuallity the formula is not valid any
more and the result truely is an error. IsNA would be better than IsError as
it would return the error if the cells were deleted but if you are looking up
text in a list of number of vice versa then ISNA will return false and the
formula will generate a blank when the value could be there but of the wrong
type.

CountIf will find the value regardless of it being text or number. If it
finds the value but the formula results in #NA then you know that there is a
data type issue that needs to be resolved.

I would definitly go with the formula you posted...