I have 2 columns of data, approximately 375,000 rows each. The calculations have been running for 12 hours and only 93% done. Does this make sense? My computer has a 2G CPU dual processor with 2G of ram. I cleaned out the cache, is it possible that something else is running in the backgroud or is this just Excel 07.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Calculation Time In Excel 07
- Thread starter CARBOB
- Start date
Here's a sample of the calculations. There are 19 columns and 195000 rows. The formula looks at the values in row 6 using each value in column D and returns the number of matches from another worksheet. I think I will have to break the 195000 rows into more worksheets. I don't think you can post an HTML in this forum or it won't work for me.
this is an array formula, Ctrl-Shift_Enter
=SUMPRODUCT(--('PKG SEGS'!$F$10:$F$377001=E$6),--('PKG SEGS'!$O$10:$O$377001=$D7))
Last edited:
Upvote
0
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
SUMPRODUCT doesn't need to be entered as an array, it is an array.
But 195 K rows of array formulas will bring any system to its knees. Any chance you could try a pivot table to summarise teh data instead?
Denis
But 195 K rows of array formulas will bring any system to its knees. Any chance you could try a pivot table to summarise teh data instead?
Denis
Upvote
0
fridgenep
Board Regular
- Joined
- Mar 26, 2006
- Messages
- 73
Hey Carbob,
Well, at first: microsoft enhanced their functions in 2007: so you can use the function COUNTIFS() which should be faster. I can't test it because I struggle to install this version.
Otherwise it would be the best to use an additional column which includes this simple formula:
Then use Countif() to count "true". That's it.
Well, at first: microsoft enhanced their functions in 2007: so you can use the function COUNTIFS() which should be faster. I can't test it because I struggle to install this version.
Otherwise it would be the best to use an additional column which includes this simple formula:
Code:
=(F10=E$6)*(O10=D$7)Then use Countif() to count "true". That's it.
Upvote
0
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
Maybe, but the first suggestion it would make is to remove all the array formulas. Try Fridgenep's suggestion -- you need a less hungry formula if you want some response out of your workbook.
You could also try a completely different approach -- use Excel like a database and create a couple of simple queries to filter down the records you want. See this page for a link to an excellent tutorial.
Upvote
0
