Can a spreadsheet be too complicated for the computer to run

  • Thread starter Thread starter Rachie1987
  • Start date Start date
R

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?
 
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.
 
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
 
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
 
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).
 
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
 
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
 
You could also fix the values of the formulae which will not change,
and this will improve performance.

Hope this helps.

Pete
 
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...
 
We are experiencing this same issue. No fix found yet sadly.

I can answer regarding your PC specs though.

You have a single core Pentium 4 processor that is based on 8 year old technology.

You mentioned a 1.7GB hard drive. Sounds like you might be running a Thin Client. If so, this isn't a PC designed for power in the least.

Your engineers PC, a Dual Core Processor, probably is NO more than a few years old. The processor is running on much better technology and has dual cores, not just one. Basically, your processor and your CAD engineers processor, is night and day in difference. It would be like comparing a Ferrari to a Hyundai. No joke.

Aside from that you engineer probably has faster, better hard drives and faster bus speeds and not just more memory but much faster memory just to name a few additonal items. But the processor is the key here for Excel formula.
 
Back
Top