#1
|
|||
|
|||
A 20k lines Sheet calculation takes 1,23 hours!!
The real sheet of the type shown in SAMPLE (attached) has 20k lines and its 100% calculation takes 1 hour and 23 minutes !!!
What is wrong with its formulas? Are there better formulas to achieve the same results (= show values which exceed the given limit, in this case 3 occurrencies)? Thanks to anyone who can help! |
#2
|
|||
|
|||
Hi, see in attached file a possible solution...
|
#3
|
||||
|
||||
Do you need to see the intermediate results in column K:Q?
Why are you using CELL("Contents"… ? Won't referring to the cell directly do? =IF(K1<>"-",A1," -") and if you need to see a leading zero, custom format those cells with 00. Direct result without intermediate: =IF(COUNTIFS($A$1:$G$9,A1)>=$I$1,A1, " -") |
#4
|
|||
|
|||
Hi, in the linked file are two tables with 21000 rows.
SAMPLEorig21000rows.xlsx (with your formulas) SAMPLEnew21000rows.xlsx (with my formulas) The product of my test (100% calculation): SAMPLEorig21000rows.xlsx: 3 minutes SAMPLEnew21000rows.xlsx: 2 seconds SAMPLEs21000rows.zip - Google Drive Last edited by fjns; 11-06-2022 at 02:52 AM. |
#5
|
|||
|
|||
Dear P45cal your direct result formula does not decrease the duration of the calculation.
Dear Fjns, I'm VERY SORRY, … to make the sample for you as simple as possible I set up a "2-digit-sized-values array" … hoping to get a solution for "any digit sized values array" … As a matter of fact my application happens to deal with a 4-digit-values array as well as a 6-digit-values arrays … so in this case your solution, limited to a 99 lines help-column, does not seem to work for the 4 and 6 digit values … attached the real type array. AP |
#6
|
||||
|
||||
A formula:
=IF(COUNTIF($A$1:$G$21000,A1)>=$I$1,A1,"-") in cells K1:Q21000, working on random 4 digit numbers in A1:G21000 (not random generated at each calculation, just plain, static values) takes about a minute and a half here. Do you have an old machine? What are you aiming to do (in words)? |
#7
|
|||
|
|||
Hi, in the linked file is the solution for 4 digits.
Each calculation takes 5 seconds. Loading Google Sheets |
#8
|
|||
|
|||
hi, P45cal, I find it difficult to understand what happens on my PC, an Acer Aspire with an I7-5500u and 8GB ram ... your solution depending on type of changes sometimes works sometimes not ... e.g. when I try to modify the filter limit o to free the first line to insert a description. Due to limits for uploadable files I re-attach only a small part of the real file (6,5 GB) with your solution ... , can you please verify yourself? thank you.
Dear Fjns, the same happens with your solution with the full 20k lines file ... also for you re-attached part of related file (8GB). You not being able to see the real file will probably make useless any effort to solve the problem ... try to increase yourself the file up to 20k lines ... and hope to identify the problem and solve it ... In any case, Thank you both very much for your help. AP |
#9
|
|||
|
|||
Hi, 10000 cells are required in columns J-K regardless of the other columns.
|
#10
|
|||
|
|||
Dear Fjns, I've noted that apparently you gave me the same function as suggested previously, but this time the 20k lines calculation went surprisingly and satisfactorily fast:
2 seconds by changing the minimum selection limit value, 4 minutes to eliminate or insert a line, also 4 minutes to change a formula in a cell! Which factor has triggered such a beautiful result will remain for me a distressing mistery! Yet the solution of this very penalizing problem of mine make me greatly happy. ... also the solution suggested by P45cal worked equally fast ... therefore a very very very big thank to both. Your grateful AP |
#11
|
||||
|
||||
I'm not sure what you mean by this statement?
|
#12
|
|||
|
|||
Dear 45Pcal, wanting to select only values which were >= of a given selection filter, by changing this filter the solution run ok ... by inserting a new line above the first existing line to input some descriptions the sheet had to be stopped because for some unknown reasons it was taking a lot of time. But do not worry ... now everything runs ok. Thank you! AP
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to adjust Project Duration for Summer and Winter Hours - based on number of hours per day | Lou C | Project | 2 | 10-03-2021 09:35 AM |
Complicated calculation of hours worked | APreston | Excel | 1 | 05-09-2016 04:42 AM |
Very complicated calculation of total hours worked (PLEASE HELP) | APreston | Excel | 1 | 05-08-2016 02:58 AM |
Complex calculation from multiple sheets to master sheet | inreality01 | Excel | 49 | 01-06-2016 03:09 PM |
Help with 'hours worked' calculation... | Snvlsfoal | Excel | 1 | 08-11-2011 05:54 AM |