Large spreadsheet / very slow calculation / faster computer - QUESTION

[ExcelKnut]

I'm working on a spreadsheet (Excel 2010) with 100,000 rows and over 50 columns. About 15 of those columns have formulas. It's slower than a dead yak. Is it "normal" that it takes over ten minutes to calculate? Would a better/faster computer help? My current laptop has the specs indicated below.

Processor: Intel(R) Core(TM) i5-2520M CPU @ 2.50 GHz 2.50 GHz
RAM: 4.00 GB
System Type: 64-bit OS

Any advice, suggestions etc. is greatly appreciated.

Jeff

 

[RoryA]

What are the formulas?

 

[SpillerBD]

Depends on the formula types. Array formulas will always take longest to calculate, but even 100k rows will take a little bit, x15 columns.
If you have any "calculate once" formulas copy and paste values, or have the formula performed in a macro that writes the result.
Hopefully you are using manual calculations....

 

[ExcelKnut]

See some of the formulas below.

=IF(ROW()=B10,"Total","")
=MAX(ROW($C$3:$C$100001)*($C$3:$C$100001=C7))
=IF(A8="Total",IFERROR(Q8-J8,Q8),"")
=IF($A8<>"Total","",IFERROR(Q8+0,"no adj"))
=IF(LEN(BJ7)=0,"",IF(BJ7=2,ROUND(BA7-AZ7,2),ROUND(OFFSET(AY7,0,BJ7)-BA7,2)))

 

[RoryA]

What do you use this for:
=MAX(ROW($C$3:$C$100001)*($C$3:$C$100001=C7))

Also, you should avoid OFFSET as it is volatile and will recalculate whenever your workbook does, even if none of its inputs have actually changed.

 

[ExcelKnut]

I use that formula (MAX...) to get the row number of the last occurrence of the values in column C. Thanks for the info about OFFSET. I'm sorry to hear that about OFFSET--I've found it very useful so I use it a lot.

 

[RoryA]

I use that formula (MAX...) to get the row number of the last occurrence of the values in column C.

I know that - I mean what do you use that information for.

 

[ExcelKnut]

Sorry about that .

I have a list of claim numbers (Column C) and the last one has the total amount paid on that claim in Column K in the row of the last occurrence of the claim number. And the number of times the claim number is listed is not always the same.

 

[RoryA]

Can you sort the data on claim number?

 

[ExcelKnut]

Yes