Dynamic Named Range or Other Suggestions to speed up workbook.

[Brian.Aerojet]

Hi All,

I have a sample workbook, in the original it will continue getting bigger and bigger.

I would like to figure out how to keep it from slowing down so much as I put more and more data in the workbook. (The sample workbook isn't slow, its only the original) I tried uploading it, but wasn't able to.

Is using Dynamic Name Range the way I should approach this, or should I look into something else to make sure it doesn't slow down so much?

I would like to stay away from VBA for the simple reason I know nothing about it.

If using Dynamic Name Range or other, can you help me to understand where to start. Im not very familiar with it.

The example is in the attachment.

Any suggestions are greatly appreciated.

Thanks,
Brian

[tim201110]

the best way is a Table and manual calculation

[TMS]

There are no formulae in your sample workbook that use a Range. That is, all formulae refer only to one or more cells on the current row. There is nothing that you could use a Dynamic Named Range for. You could, maybe, convert the data to a Structured Table.

However, if the data is static, you might do well to use Copy and Paste Special | Values to remove the formula in the historic data.

[JohnTopley]

Try this in D3

=IF(WEEKDAY($A3)=1,"-",IF($A3<>$A2,$F3,"-")


Copy down

Removes COUNTIFS which can impact processing

EDIT: corrected formula

[TMS]

@JT: that doesn't appear to produce the same outcome as the original.

[JohnTopley]

My posting error: sorry!

in D3

=IF(WEEKDAY($A3)=1,"-",IF($A3<>$A2,$F3,"-"))

[Zer0Cool]

A few things you may do to speed up calculations.

Column D and E use the same COUNTIF (or in @JohnTopley formula the same inner IF):

Instead use a helper column with that formula and then reference that cell from columns D and E

IE: new column K3 would be:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Then in columns D and E:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

This way you are evaluating that condition 1 time for both columns, half the calculations.

I also agree that the formula given is better, presuming your dates are in order like in your sample. Otherwise stick to COUNTIF if your dates are mixed up and you dont want to sort.

Also column F is a bit redundant. The value already exists in C:E and looking at the sample I presume only 1 column per row will have a value. Ill come back to this...

Column G should be (likely a minimal gain but should work a bit faster):

Please Login or Register  to view this content.

Columns H, I, J are all pretty complex for dealing with only 3 characters of which we know the positions of. In your actual data are the numbers from C/D/E and/or F always 3 digits and you break them down to 1st, 2nd, 3rd digit in H:J?

If so:

H

Please Login or Register  to view this content.

I

Please Login or Register  to view this content.

J

Please Login or Register  to view this content.

So going back to Column F. You could eliminate it and instead update the H:J formulas to simply evaluate C:E and grab the number. something simple like:

H

Please Login or Register  to view this content.

This should work as only 1 of the 3 columns has a number and any number plus nothing is the same number. In this fashion you dont even need to locate the column. You could update I and J in a similar fashion.

However it would be even better to convert column F to the SUM(D3:E3) and this way for the columns H:J we only calculate the sum 1 time instead of 3!

As mentioned, I would set the data set to be a table. If you stick with COUNTIF I would then set it to look at the whole column instead of a growing range (instead of absolute:relative) as this will simplify the calc chain.

Also, in your actual file how many columns/rows are we talking about roughly? Whats the file size (KB/MB)? Is it just one big sheet or many sheets?

If its one sheet, click in cell A1, press CTRL+END. If it takes you to the bottom right corner of your data, great...if it zips past you last row and column actually used on that sheet you have excess content you should clear as it may be impacting performance.

[TMS]

@JT: that seems to have solved it

[Brian.Aerojet]

Thanks guys and gals for the suggestions
John has helped me several times with no issues. I definitely will put his suggestions in place.
I’m working on updating what Zer0cool has suggested at the moment
I do have numerous sheets in 1 workbook A-BK columns, rows I’m at nearly 11,000 now. This is only the 1st sheet. The other sheets are much much smaller. The 1st sheet will get bigger and bigger. The others will also, but they’re mostly taking data from 1st sheet and breaking things down in the other sheets. As of right now my workbook is 7.15mb and growing, but the growing has slowed way down because I’m not able to add anything without it taking 30min to update.
TMS I haven’t used the special copy and paste in the past, I will definitely look at what I can improve by using this suggestion from you. Thanks for info.
Tim I haven’t opened yours yet to look at it. I have issues while trying to opening excel from where I work. I have to do it on my phone, but I definitely will look into it soon as I can. Thanks.
Tim, I did get to glance at yours on my phone in my excel on my phone.
I’m liking your idea also,
I do have a question to you, what if I use this same way through 1million rows? Will I need to do anything different? The reason I ask is over Time this thing will grow tremendously.

I’m going to leave this post open for now and not show solved until I can get home and pull it up on my home computer with the suggestions I’ve been given.

Thanks I will reply back with an update sometime later tonight

[JohnTopley]

what if I use this same way through 1 million rows?

If your posted file is a typical example then a million rows is over 1000 years of data: forward planning to the extreme!!!!

In reality, I suspect in many cases, any planning over 5 years is questionable. and 10 years is very futuristic!

FYI: my suggestion on COUNTIFS was based on a recent observation by one of our gurus ("FlameRetired" - thank you!) that the incremental COUNTIFS [ i.e COUNTIFS($A$2:A3) and dragged down a column] had a impact on performance whereas the COUNTIFS($A:$A,B2) is deemed to be efficient..

[Brian.Aerojet]

Im going to try and upload my full version
This will have all data since 3/1/2004 on it
I think one of the big things that I don't understand is when to use general, text, number, etc in the column.
The reason for this, I need to have the zeros always be accounted for in the calculations

[JohnTopley]

Why do you have data going back until 2004 ?

If it is (and must be) historical, then archive in it (yearly?) workbooks rather continue to do what must surely be totally unnecessary calculations.

[Brian.Aerojet]

Im still trying to upload original workbook, any suggestions on how I can get it to upload?

How do I archive the older data?

[JohnTopley]

If you insist on uploading "history" try ZIPping the file: but see my previous post.

[Brian.Aerojet]

Thanks John, I will work on this.

I wish I could have got online sooner today, but ended up working all night last night, and headed back to work now.

I think archiving might be the answer, Im not familiar with how to accomplish this without getting reference errors on my data that I still have on the worksheet.

What is your suggestion on this?

[Brian.Aerojet]

Zer0cool, I could not get your calculations to work when I entered them as you suggested.
Can you upload an attachment of my file with your formulas in place?
Thanks

[JohnTopley]

Re archiving: The reference errors could be "removed" by copy/paste special of the data to a separate worksheet/workbook.

We really need a file (cut down to 2 years max) if it too big to ZIP

[Brian.Aerojet]

Here is my latest update

I had to narrow it down because of the 1000kb upload rule for excel forum

[JohnTopley]

I have managed to eliminate the COUNTIFS in columns C:E

in C2

=IF(WEEKDAY($A2)=1,"",IF($A3<>$A2,$F2,""))

in D2

=IF(WEEKDAY($A2)=1,"",IF($C3<>"",$F2,""))

in E2

=IF(WEEKDAY($A2)=1,F2,IF($D3<>"",$F2,""))

..but the "empty" cells are blank rather than "-"

I have no idea what the formulae in W onward do but I am not surprised the performance is poor with all the SEARCH/INDEX/MATCH combinations.

[Zer0Cool]

Zer0cool, I could not get your calculations to work when I entered them as you suggested.

Your new sample is much more complex than previous. Can you explain in detail what each column is meant to do, what the workbook as a whole is meant for?

Looking over it, without the context of headers to explain what each column is for or knowing why you are doing all of this many of your formulas seem overly complex.

C:E is a good example of this. As you have it its 2x IF's nested with a COUNTIF in it. Even with @JohnTopley's update its still a nested 2x IF but I can figure out why.

All 3 use the first IF to evaluate if WEEKDAY(A) = 1, with C and D being "-" if it is and the last being F if it is. Every single row that is Weekday = 1 has a value in column E, so it appears the added IF(COUNTIF isnt required unless some pattern not in your sample occurs.

The only difference between C and D is if the COUNTIF is 2 or 1. You could simplify all 3 by using each one to get the next instead of all 3 performing the logic.

IE:

E2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

C2

Please Login or Register  to view this content.

1/3 the number of WEEKDAY calculations
No nested IF's (AND should be faster than nested IF's)
No COUNTIF's (themselves efficient, but not as much as simple operands on single cell ranges).

I dont get the purpose of F and G...why both columns. You could have 1 column and format as text with 000 to the same effect. 1/2 the number of cells, same result.

Column H (instead of ++)

Please Login or Register  to view this content.

I:K go back to my other post. Keep it simple. You dont need mid for getting the first leftmost character or last right most. You dont need the COLUMNS formula on a growing range. LEFT/RIGHT/MID are text functions, they return text already, dont need the TEXT function to format the output, use cell formatting as with F/G.

From L and beyond I cant even start to give advice as I have no idea what the intention is.

It seems to me that most parts of this workbook have been made overly complex instead of using the simplest solutions. Thats good and bad. It means whoever did this knows more than the sheer basics, which is good. Finding the simplest solution for a problem in Excel is what makes someone great at Excel though and this is far from being simple.

EDIT: It may be really worth your while to go back and re-evaluate what this file is and what it needs to do. Maybe even rebuilding it from the ground up instead of trying to work over the existing copy. I know it doesn't sound pleasant, but Id consider keeping your current copy, starting a new blank file and figuring out exactly what you need to do in terms of what data you need in the file and what needs to be done with the data. You may find this way that parts of the file are remnants left from a time long ago and are not required any more. You may on the other hand identify new needs not handled well/at all by the current file that you can easily build into a new file.

[JohnTopley]

@Zer0CoolYes .. I considered the AND option: I don't know if it is faster than the IFs but should be removing the WEEKDAY test on 2 columns.

Like you I don't understand the intention most of the other formula but they certainly must contribute to the poor performance. And why is the actual production workbook going back to 2004?

You may be correct in saying let's go back to basics to see if the whole shooting match can be simplified.

[Zer0Cool]

@Zer0CoolYes .. I considered the AND option: I don't know if it is faster than the IFs

AND can thread each evaluation, IF can only evaluate each nested IF one at a time. IE:

The AND can use a thread each for A1, B1, C1 (3 threads) and still stops as soon as it finds a false:

Please Login or Register  to view this content.

The first IF (A1) needs to be tested before being able to test B1 which needs to be done before C1 can be tested. You cant do them in 3 simultaneous threads as each test is a condition of the prior.:

Please Login or Register  to view this content.

Its not likely a huge difference in less complex nested IF's, but better to eliminate it as an issue.

[JohnTopley]

@Zer0Cool: I tried your formulae from post #20 and they do not it give correct results.

[Brian.Aerojet]

Zer0cool,
Sorry for not adding headers, I know it is hard to get help if someone does not give good info. I will try to explain in detail below what each column does.

Im keeping Data of past pick 3 draws

Column A, you will notice for example the dates show the same date 3 times except for Sunday which only shows once, the reason for this is each day you have 3 draws except for Sunday.

Column B, this is the time of day of the draw. Morning, Midday, Evening. Evening is only one you will see on Sundays, the Morning and Midday are only Mon-Sat.

Column C is Morning only drawn numbers, Col D is Midday, and Col E is Evening drawn numbers

F and G are the same, Its shows all the drawn numbers regardless of morn, mid, eve or day. This column is in order by the date in col A.

The reason I had F and G both was for the "0" if it`s the 1st number, I could not get it to show in Col C, D, or E when it was the 1st number. I had same situation in col I, J, and K with the zero not showing.

I need to always show the zero if it is one of the numbers drawn regardless of 1st 2nd or 3rd number drawn.

Col H is a sum of the 3 digits as single numbers which I break them into single numbers in the next 3 columns I, J, K.

I will explain the next columns in the next post, I don't want to make post to long. It would be to confusing.

[Brian.Aerojet]

L1 through U1 is a number line of 0-9
Starting in L11, which is under the "0" in L1, the formula looks back at the last ten 3 digit numbers to count how many times the "0" shows up and puts that number in L11.
The same thing for M11 which is counting how many times the number "1" shows up and puts that number in M11. This pattern continues with the other numbers up to number "9"
As I copy and paste more rows, the formula always counts back ten 3 digit numbers.

The next column is V. This column looks at the previous Columns L-U and the number in F or G that comes up it puts what the number was in L-U with a dash in between each one.

I know this part is confusing you and you are saying why in the heck would someone do this, lol. Ive been doing it for many years, but just in the last year started transferring it to excel instead of manually writing it down, which has been a big help for me.

Columns W through BF with the "Yes" and "No"
Its actually pretty simple. If the last number drawn is 582, the number number right before it was 871 it would have a YES in column W in the same row as the 582 number. but if the number before would have been 971 it would have a NO.
The YES means one of the digits in the drawing before match the drawing that is now, like 582 matches 871 because of the 8, but none of the 971 match the 582

The other columns beside the YES and NO are counting the YES, and No`s

Hope this helps a little bit
Thanks

[Zer0Cool]

For Columns A:K check the attached. Much simpler. Let me know if that meets your needs for those columns.

My changes are on Sheet 2. I copied over your base data and built the formulas from scratch. Changed the order to make a little more sense to me for your process (trying to do stuff in order left to right).

Ill read your 2nd post and continue with the changes

[Zer0Cool]

Columns W through BF with the "Yes" and "No"
Its actually pretty simple. If the last number drawn is 582, the number number right before it was 871 it would have a YES in column W in the same row as the 582 number. but if the number before would have been 971 it would have a NO.
The YES means one of the digits in the drawing before match the drawing that is now, like 582 matches 871 because of the 8, but none of the 971 match the 582

Can you please clarify on this. I get the running counts, but the yes/no columns I do not get.

Starting with column W (last one BC), you have 9 columns of the Yes/No's. What is the intended purpose here, what information are you trying to get? I am not interested in what the current formula does, but instead what you want the result to represent in those columns.

As you stated it in the part I quoted, it sounds like 1 column is all you need. The previous drawing either has a digit that matches the current drawing or it does not. The only reason I can see for the 9 columns of yes/no is if you want a match per digit (0-9). Is that the case?

If so, why not just have 3 columns then; matches first digit, matches 2nd digit, matches 3rd digit?

Current number is 582, last is 871. First col is does 1st digit of 582 appear in 871 (no), Second col is does the 2nd digit in 582 appear in 871 (yes), and Third col is does the last digit of 582 appear in 871 (no).

Also, I can tell you the INDEX formulas you are using in W and off to the right are likely a big issue as you reference $BJ$2:$BJ$999168 multiple times in each formula.

Once I understand what you want to show with these I can simplify the formula and add it to the attached sample to complete it. Sheet2 up to Column U is complete and working as far as I know. Ignore column W:AE for now. I have for the sake of file size removed your Sheet1 in the sample (also worth pointing out my sample is ~300KB vs almost 1MB from your original. Id be surprised if it passes ~500-600KB when I am done).

[Brian.Aerojet]

Post #26 is 100% perfect Zer0cool,
You have made this look great on sheet 2

I will read your next post and try to explain it better for the next several columns

[Brian.Aerojet]

Everything looks perfect,

As far as starting in W Column, you can make it the way you suggested with the 3 columns.

The reason I was doing the other col was the current drawing will look at the previous drawing for a match of those 3 digits that is col W, the next col would match the current drawing with the 2nd previous 3 digit number. Then the next col the current drawing matches 3rd previous drawing and so on until the 9th one back.

Ex. 145, 190, 083, 345, 678, 743, 032, 645, 923, 210
The 145 is current drawing
Col W looks at 190 only for a matching digit, so this one would be Yes
The next col still uses 145, but it now looks at the 083 for a matching digit, so it would be no.
The next col after that still uses same current drawing 145 and looks at the 345for a match, so answer is yes.
And so on,
Once the next current drawing comes out it drops off the last 3digit number which is 210
And repeats the above pattern. So if the next draw after 145 is 096, then back to col W, this time it would look at the previous drawing which is now 145 and the answer would be No for the next row.

I know it does not make since, but it is very useful in playing the pick 3 lottery. I have found so many patterns over the years, I’m just now trying to put them all together in excel workbook. I have so much more I’m working on with this, but what you’re doing for me is making it all start to come together and very understanding. My next project is using same kind of worksheet, but it will be only for morning, then one sheet for midday and then another sheet for evening.

I also love how you have made the table with the headers. It works perfect.

I have a question, this sheet is only 1 year of past drawings, could I still add other years to it and it still be fast without slowing down? I have 14 years of Data for the pick 3 I would like to add, but if I need a different sheet or workbook for each year I guess it would still be ok. Just a thought

[Zer0Cool]

As far as starting in W Column, you can make it the way you suggested with the 3 columns.

The reason I was doing the other col was the current drawing will look at the previous drawing for a match of those 3 digits that is col W, the next col would match the current drawing with the 2nd previous 3 digit number. Then the next col the current drawing matches 3rd previous drawing and so on until the 9th one back.

Thats exactly what I needed to know. So I can do it either way. We can do 3 columns representing 1st/2nd/3rd digit matching back 1 drawing or like the original any digit matching back historically 9 drawings. I will stick to the original method for now that I understand it.

Do you really need the running count of yes/no's and difference in them on each row?

I have a question, this sheet is only 1 year of past drawings, could I still add other years to it and it still be fast without slowing down? I have 14 years of Data for the pick 3 I would like to add, but if I need a different sheet or workbook for each year I guess it would still be ok. Just a thought

I am designing the sheet with lots of data in mind. However no matter what we do more data means more time to calculate. The key for you will be what is a reasonable amount of time for a data set to calculate and can you use manual calculation or do you need automatic. So you may for example be able to dump all the data into a single sheet, then decide if the calc time is reasonable left automatic. So lets say hypothetically it takes 30 seconds for a recalc of the data, if thats reasonable and you want to reduce the frequency that it updates you can set it to manual to control when it takes that long to updates formulas. Alternatively if it takes 5 mins then you may decide even at manual calculation thats just too long. You might then consider breaking down the data into chunks (a file for each 5 years, 2 years or 1 year, etc). Splitting it across sheets wont help, if anything it may make it slower. Essentially you will need to decide what the balance of performance vs number of data sets is.

So I would say once we work out the logic and ensure each column accurately represents what you want and has a purpose that you make a copy of the file using my setup and dump all of your data into it. See how it responds and then split it down into multiple files if needed.

Lastly, as someone else mentioned earlier in the thread, consider stripping out the formulas on either older data or all your data. For example once a formula on a row has set everything up, does the formula still need to be there or do we only need the results? Stripping the formulas out will have the biggest impact on calc times as cells without formulas do not need to be calculated. This can be done by simply copying a data set and then pasting over it using Paste Special | Values (or values and formatting). I would only recommend this if the results of the formulas will be static and you keep a sample around with the formulas to reuse as needed.

Ill touch up the file and upload back soon.

[Zer0Cool]

See attached with the 9 columns similar to W from your original. Didnt do the running counts, we can add it if you like but thats 27 additional columns (count of yes, count of no, difference x 9 yes/no columns) that I didnt want to do until I know you needed it. We may even be able to simplify those formulas vs your original. Let me know if you need that.

Attached file is ~380KB vs ~1MB from your original. Same number of rows of data, a few less columns...but overall much simpler formulas. Not a single formula looks at a range larger than 30 cells, whereas you had formulas that looked at almost 1 million cells multiple times ($BJ$2:$BJ$999168). We also simplified the calc chain by reducing the amount of redundant work (like calculating the same thing multiple times in different formulas) which should combine to make calculations much faster than before.

[Brian.Aerojet]

I like your idea in post #30 about the formulas, that definitely makes since to keep calculations faster.

In a way, I would like the count of Yes and No, but you don't have to do it. You have made my life 10,000 times easier with what you have already done.

If you decide to, just let me know your suggestion of the formula to use and I can set it up.

Thanks again Zer0cool, you`re one in a million

[Zer0Cool]

I did the first 3 of those running count columns for you as an example of how best to do them. Notice in this running count that you only ever reference 1 cell, instead of an ever growing range of cells. It should be much more efficient this way as we aren't recalculating previous sum'd values again and again.

That should give you the tools you need to build out the rest of those columns, then rebuild your actual file as we have done in the sample here and put your data into it to see how it fairs in terms of size and calc time. Let me know how it turns out.

Went from ~380KB to ~440KB adding the 3x columns, so roughly 60KB per 3 columns, 24 more left to do. Should be approx another 480KB so about 920KB when complete. Thats about the same size as original file size but should be much simpler calculations to the same result. It may also be less compressed making it faster to open/save/close.

[Brian.Aerojet]

Thank you Zer0cool

Everything seems to be perfect.
It works and looks so much better than I had originally visioned.

This will definitely help me on future excel formulas.
Again, Thanks for taking the time to help me.

I’m going to show thread solved now. I went ahead and added reputation yesterday.
Brian

[Brian.Aerojet]

Zer0cool,

How did you get the numbers in column C to show the zero in front of the numbers like 045?
Did you enter them manually or copy and paste?
When I tried to copy and paste more numbers in column C the ones that have the zero in front did not show the zero.
Do you have a suggestion?

[Zer0Cool]

Thanks for the rep.

O yea I forgot I used a helper column temporarily to convert the numbers to text. Something like:

Please Login or Register  to view this content.

also have the table column (not whole sheet column) formatted as text. I copied the results from the formula and pasted values over the originals then removed the helper column with the above formula.

You may have noticed in the sum of digits I have each *1. That converts them to numbers inside the SUM function (or in any function for that matter) specifically because you cannot add text.

[Brian.Aerojet]

That works for me, thanks Zer0cool

[Brian.Aerojet]

Zer0cool,
I tried what you suggested in post #36 with a new table I’m making for 2018
I’m not having any luck with the helper to make the zero show out front.
Can you show me exactly what you suggest with the choose formula, I must be doing something wrong with it.

[Zer0Cool]

See attached

Please Login or Register  to view this content.

Formula starting in D2, filled down. You then copy the results of the formula in D and either paste them as values & formatting to your destination or over the original numbers. Then you can delete the formulas in column D

[Brian.Aerojet]

Thanks Zer0cool, I knew it was simple. I just could not figure it.
I’ve been trying to learn so much other stuff you and others have taught me.
After a while it all starts running together, and I get confused.

Thanks again for help