• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel can not handle it

Harry0

Member
Despite a powerful computer my excel can not handle it and takes a around 1 minute to be able to input something and for everything to change. It has to computer 1000 clients for to arranged on a daily schedule and no 2 schedules are alike which has to go thought seriously over 100,000 computations.
I tried to limit the range where it can look up the row but still takes a while. The file is a little over 3mb of formulas. so can't upload since it is big.

Do you think excel can handle it?
 

David Evans

Active Member
Well - without being able to see much of what you're experiencing, it's hard to give you a definitive answer, but if you're having trouble entering data, have you turned off Automatic Re-Calculation? That can help with data entry slowness in my experience.
Perhaps you can show some of your formulae to see if there's a more efficient means of calculating them?
In and of itself, 3Mb is not outrageous ....
 

bobhc

Excel Ninja
my excel can not handle it and takes
There could be many reasons for this, even though you say you have a powerful computer many things could be happening in the background that are hogging the resources.
3mb of formulas
If you really have that much in formulas that are computing 1000,000 times I think your computer is doing well, and who ever made a workbook with that lot in should at the very least be shot, well maybe sacked.
Do you have volatile formulas that are slowing everything down?.
Without knowing what is going on inside your workbook it will be difficult for members to help.
Can you upload to Dropbox and then publish the public link here.


.
 

jeffreyweir

Active Member
Here's some draft content from my my forthcoming book, Excel for Superheroes and Evil Geniuses which will have an entire section devoted to troubleshooting slow files:

Excel’s most powerful tool – it’s mighty Formula engine – is so easy to use that we often misuse it. Sometimes so badly that things slow to a crawl. And then we blame Excel. But here’s the dirty truth: Excel is blazingly fast…unless we’ve programmed it very inefficiently. (That’s right, you’re a programmer, and Excel’s formulas are subroutines.) Excel only stumbles if we’ve added the formula equivalent of Kryptonite to it.
And here's some posts I've done over at Chandoo.org that you should read, because they can probably help you remove calculation bottlenecks:
 
Last edited:

Harry0

Member
so close to 4 mb which was mb since i erased some info and shortened the name and info to 3-4 digits.
I do not use any of the Volatile functions. But it is fat which most of the functions I obtained here from the others which it was the only way to do it... so it seems. It is obviously well over 100,000 calculations.

In what the link says to only use the data that I should consume which

If still is not perfect since it slows down for only 2 months and still is missing plenty of formulas for each thing. It is as usable as it should be since I can add a new clients or change an existing client day of the week or the amount visited and everything can get disorganised which throws everything off which I still did not find a way to handle that. which is one of the main issues now to make it unusable.

Even changing a value takes up 100 processing power on this Mac 2.7 core I5 with 8gb. Obviously not the best but still a powerful computer. Using excel 2011. I tried to load the file on a older windows core 2 and it just crashes.

First worksheet is the contact which increases, 2nd one is just a different format of it, the 3rd is the month of November, then december, and future days for the rest. And this is only calculating 1/3 of the contacts which which the options to interchange the other 2/3 does not seem as plausible at the moment.

File uploaded here: http://expirebox.com/download/de2451644e51061699976a176597cd81.html

If it is giving slow down just for a fraction of the info put in it will be worse if it does it all. I am thinking that it is too complex and big for excel to handle.
I might have to go a different route than use excel since excel has been very time consuming for over a month now with little results.
Thanks for trying
 
Last edited:

David Evans

Active Member
Yup...I'm milking this yet unwritten book as much as I can!
And you milked me for the book - well Amazon did! That's quite an achievement to sell a book that isn't written, and I bought it from a company that cannot turn a profit ... I must be totally stupid!:confused:
 

jeffreyweir

Active Member
HarryO: I've had a quick look at your file. There are many, many things you could optimize. I'm working on a draft post at the moment that covers some of them, which I've uploaded to http://1drv.ms/1xAHTDj (I can't seem to upload it here, for some reason.).

With the guidelines in that draft post, plus a few more suggestions from the folks here, you should be able to get that file to recalculate in milliseconds. It only takes so long because of the way you have programmed Excel.
 

jeffreyweir

Active Member
Dave - make sure you download that file from my dropbox link posted above. It's a sneak peak at some of the stuff to do with efficient formulas. Plenty more where that comes from. That stuff is just the tip of the iceberg.
 

NARAYANK991

Excel Ninja
Hi Harry ,

There are two aspects to workbook heaviness ; one where the workbook is a well-optimized one , but is burdened with the tasks that it has been developed to handle ; the other where the workbook has grown organically over time the way a tree develops roots viz. haphazardly and in all directions.

A workbook is supposed to be planned well , not just for immediate needs , but anticipating what might come in the future.

I leave it to you to decide into which category your workbook fits.

However , once a workbook has assumed such dimensions as yours has , the way to optimization and speed is uncertain , since for a outsider to understand the intricacies is going to take time ; in such cases , use can be made of software tools such as profilers ; one such is available here :

http://ramblings.mcpher.com/system/app/pages/search?scope=search-site&q=optimize

From the little I have read , I cannot emphasize the utility of the body of work that this author has put out in the public domain.

Running the profiler on your workbook took almost an hour !

The results are stored back in your file , along with the code.

Study the results and see how you can improve the formulae in the columns which at present are contributing to sluggishness.

The workbook is here :

https://www.dropbox.com/s/16g0aprj9hq0zbw/Harry0_contacttemp.xlsm?dl=0

Narayan
 

Harry0

Member
Well everything could be better.

Nara it seems that the biggest slow down is from the formula you gave me in the other post http://chandoo.org/forum/threads/condition-results-for-multiple-columns-sorted-and-compress.20089/#post-121224
Which is a good formula and no one else gave a better solution so that is the best solution.
Array Columns like for example Nov BM take the most and then K.

BM which has 10 things to calculate in a row out of 2100.
=IFERROR(INDEX($BK$2:$BK$2100,MATCH(SMALL(IF(LEFT($BE$2:$BE$2100,3)=$BM$1,IF($BK$2:$BK$2100<>"",$BL$2:$BL$2100+$BK$2:$BK$2100%%%)),ROW(BD1)),$BL$2:$BL$2100+$BK$2:$BK$2100%%%,0)),"")
and K
=IFERROR(INDEX($BI$2:$BI$2100,MATCH(SMALL(IF($BE$2:$BE$2100=$F$2,IF($BI$2:$BI$2100<>"",$BG$2:$BG$2100+$BI$2:$BI$2100%%%)),ROW(BD1)),$BG$2:$BG$2100+$BI$2:$BI$2100%%%,0)),"")
at least 2 billion calculations within the file alone.

And the next is the standard vlookup.
Also the fact that a "0" or error message of no result instead of a blank cells is not the default value when nothing is present can cause problems.

So if I take out the standards that is used a lot and a formula that no one else has a better solution to what is left is that excel can not handle it. Obviously I am annoyed since its been over a month and no one seems to have a solution to what I say and when I did find one it should not exist. LOL

Obviously it is over 100,000 of formulas a worksheet since one cell can consist of over 1 million formulas to be in a second, and that does not insult the amount to rows and columns it has to look throughout which is over 2,000 rows and at least 2 columns. which would be at least 4 billion a second. Core I5 can do over 40,000 MIPS so it should be enough but it's not. Maybe it's MIPS in binary which takes up most of the process? 0 1, 0 you 01 Obviously a silly assumption.
Well if excel can not handle it can a program dedicated to it handle it? Scheduling software companies say wow we can not handle that and its too advanced... for them. To say that a specific place to be place should be visited every x weeks too advanced. Funny its easy to do by hand but hard for a computer. Well so it seems with excel.




Warning Rant...
MS office 2000 in some ways do more than the latest version which is almost typical of MS devolution. I can copy and replace various complex formulas a second while this is limited in some ways or not as user friendly. Let alone windows search option which has gotten clunky/useless.

Well Microsoft has been making things worse over the years as if to stifle productivity to make things worse like the user interface to programs how they are inconsistant ands clunky, and even there flagship products like windows 8 being considered the worst OS of all time, the annoying ribbons in office. xbox one which they can not count to 3, but the next windows can not count to 9. But MS is known to not not go to standards or even be comparable to help with productivity which is one reason why most do not like designing for IE to let FF and Chrome lead and is only staying afloat due to people assumption that all are the same or the best is the one that comes with the PC.

Good things people like jeff has a book to sort it out in how unsure friendly things are. The notion of productivity went out the "Windows".

The funny thing is that besides the #1 things downloaded for Mac's is anti virus, the #2 is MS office, so that indicates how unresponsive and/or useless the other programs are.
Buy Jeff's book people, which MS site can not even explain how their programs work as clear as others since MS don't get it or know what they are doing.
 

NARAYANK991

Excel Ninja
Hi Harry ,

The issue is never the formulae ; the issue is that we never plan for what is going to come.

When people approach a forum for any problem , they very rarely explain everything ; in general , you will find people who already have an approach or even a solution in mind , and only expect others' approaches / solutions to confirm their own thinking.

I am not a supporter of complicated single-cell formulae ; I very strongly urge every other person to use helper cells / columns / rows / even worksheets liberally , since though it makes for a lot of effort , it makes an application , which is what a workbook is , easier to implement , easier to follow , easier to modify , in short easier to work with.

What is required is a problem-solving approach , not an Excel worksheet or workbook approach ; Excel is merely a tool. And when not used properly ,.... a good workman never blames his tools.

You know your requirements best ; you need to sit down , and see how best you can remedy the situation at this stage ; it is not too late. If it means starting afresh , it is still better than living with a problem ; with just 2 months of data and formulae , your workbook is having problems ; imagine what is going to happen by year-end.

I am absolutely sure that if you can come up with a detailed , comprehensive functional description , together we can come up with a properly planned Excel workbook , which will do all that you want , without the sluggishness that you see at present.

Narayan
 

Harry0

Member
Your points are all valid. But in business time of the essence with the least expenses at mind. A tool is only as good as the user is the general rule but the tool always updates since the user that makes it understands its limitation. I am only stating how MS is not on the ball to make it better but worse in some aspects, and if they keep trading off to add some good with some bad then this forum will still be around for a long time, which I do not mean it in a good or bad way. It takes 2 to tango and part is obviously my limitation and the formulas limitations which should do more but I need to add extra BS to make it work.
Obviously my thing is a bit complex in the scale of it so the bigger something is the more errors will happen, and more power consumption. It is my 4th variation of the file in the many ways I am trying to execute it. I explained it multiple times on this forum in different ways with no result while in emails to scheduling software companies they got it but say they can not do that since its too complicated for them despite they are in the business of it.

I said you honestly did a good job and that does not dismiss the fact that MS which sets the standards sometimes is not putting as much more practical and user friendly options. Forums like this make MS official site seem that they are incapable to have a better user interface when that is there primary job.

But that goes for any company that if they spend 1,000,000 hours on extra things to make it more used friendly, more time is saved on the millions of people that have to even waste even one extra 1 minute to deal with it. Obviously more people use it and/or more time is wasted.

Single cell or helper cells does not make as much of a difference sometimes sine the same amount of calculations are done. My math was off in how much is calculated since I forgot the amour of rows which is far more than 4 billion a second. If anything helper cells will slow down since I would need to use the =cell all the time to have another sheet to properly present it which will need more calculations. Helper cells do make the code simpler to manage. Unless you feel single cells formulas take up more power.

My rant stated some facts with some opinions which are not wrong for the most part. We can't excuse MS on everything since a lot of people seem to find it that they are messing up in many ways is what my side rant is about. Even MS help site on excel is very dull and limited in its explanations. The more people post on sites like this indicates the wrong direction MS is going while if less people posted the better direction they would go in the product being more user friendly, if both situations have the same amount of people using excel. Sometimes I see simple comments which maybe people are lazy but also it can be that it is not well presented by MS is the main thing I am saying.

The thing is that obviously it is not easy to have something user friendly and it is the reason why some companies do well or fail in how user friendly it is. Which is why MS smart phones are not doing as well as android, and apple lost a big chunk. And everyone is trying to keep updating it to make it better. MS use to be a step ahead of others and now it is a few steps behind others.
 
Last edited:

jeffreyweir

Active Member
@Harry0

Obviously my thing is a bit complex in the scale of it so the bigger something is the more errors will happen, and more power consumption. It is my 4th variation of the file in the many ways I am trying to execute it. I explained it multiple times on this forum in different ways with no result while in emails to scheduling software companies they got it but say they can not do that since its too complicated for them despite they are in the business of it.
This forum - staffed by volunteers - is never going to turn up an optimal solution for a complicated model that someone has already built sub-optimally. Because we don't have time to look over every user's requirements in total and work out how to better structure their data and formulas. If you want this model to run fast, you're going to have to pay someone like me or Narayan to do it, and it's not going to be cheap: it might take up to a week to understand your requirements, and up to another week to program the model from scratch and to test it. I don't know about Narayan, but my hourly rate for ad-hoc EXcel development is somewhere around $100US per hour. So while someone like me or Narayan or other excel blackbelts on this forum could undoubtedly make your model recalculate in seconds (or even milliseconds), we simply can't do so, without ignoring all other questions on the forum that we could help out with.

If you read that draft blog post I posted a dropbox link to, you will already see that I've highlighted several things that you can do to minimize the drain on Excel for this model. Including leveraging off of Excel Tables so you don't have formulas pointing at ranges where there is no actual data entered, and getting rid of hundreds (perhaps thousands) of IF statements that do nothing more than copy data from elsewhere and suppress the display of zeros. And I haven't even considered how restructuring your data will help. So let's be absolutely clear here: You have constructed a very, very suboptimal model. Excel is running slowly in your case because you have programmed Excel to run slowly. This model would run slow on previous versions of Excel. If you were to leverage off some of the newer stuff built into newer versions of Excel - like Tables - then this model would actually run FASTER than it would run on previous versions of Excel.

That's not your fault...you did the best you could with the skills and knowledge you had. But it's also not the fault of this forum. Or of Excel 2010. It is down to your lack of programming skills. Period.


Single cell or helper cells does not make as much of a difference sometimes sine the same amount of calculations are done.
The point of helper cells is to store parts of a calculation that can be reused by other formulas. It makes a HUGE difference if you work out what calculations can be leveraged by other formulas.
 

Harry0

Member
I never blamed the forum or the members and said they did a good job due to the circumstances. So going in circles is not the solution.

If statements seem to be needed to leave certain cells blank to be printed out and filled in by hand and to leave zero, true/false, or other messages makes it useless, or messes up other formulas that rely on it. Obviously everything can be better from machine to person. LOL

I have as many helper items as it should, and any more will slow it down, so single cell formulas were more efficient. A lot of things were needed which as I said not even professional companies can do this and stated it was too complex for them. Tables were not used.

Even the traveling sale man problem can not be put in since the calculation alone for 1000 locations excel can not handle that old problem. Maybe one day MS will add a formula for it or maybe they won't.

A lot of things you misunderstood me and I agree generally with your comments. Obviously some things can work better with the new version of excel but my point was not everything got improved and "some" things got worse in UI or has not been touched, which is why it currently needs more people to solve things than less.

With technology if people did not point out errors how can things improve since it will only help thing remain the same it seems. Which is why MS new game system changed before it came out into the stores since "enough" people pointed out the errors of their ways than accept it since it was reported before hand what it can and can not do. In office things they never speak up as much ad when they do then the user is to blame and not the usual suspect for mess ups lately. If this continues then it will only get worse just like how Vista is worse than XP for example despite some will accept vista as better pointing out the few good and ignore the bad. Or how many say win 8 is worse than win 7. We both know if people had a choice they would stick to what they know and works than be forced to choose what OS it comes with is an example.

Well can't say all of Ms products are best MS Paint vs Adobe Photoshop. lol

Also if the user is to blame and not the tools then the first user to blame is MS developers that programed how Excel works since the tools to make it more user friendly is there. ;) ;)

Well people should read your book which makes plenty of valid points.
;)
 
Last edited:

jeffreyweir

Active Member
A lot of things you misunderstood me and I agree generally with your comments.
No Harry...I'm not misunderstanding you. You are not listening. That sample post I provided the link to has lots of things directly relevant to your file, that you could fix yourself easily if only you would read it, and think about it's relevance to your file.

If statements seem to be needed to leave certain cells blank to be printed out and filled in by hand and to leave zero, true/false, or other messages makes it useless, or messes up other formulas that rely on it
You are wrong. They are NOT needed to do that, because there is a better way. Read the section Don't use Formulas to format numbers in that document I posted. Then get rid of all of those IF statements, and apply number formatting instead.

I have as many helper items as it should, and any more will slow it down, so single cell formulas were more efficient.
You are wrong. Read the section Don't use multiple VLOOKUPS to pull data from different columns for the same lookup item. Then go and look at your sample file. For instance, in the Dec tab, you have seven VLOOKUPS in columns C through M that all look up cell K4. That is ridiculously inefficient. If you had one helper column that did a MATCH on K4 to find what position it fall in in the lookup table, then you could replace those inefficient multiple VLOOKUPS with efficient INDEX functions.

Also, read the first bit of the section Minimise the amount of formulas in your workbook - then go look at your sample file. Most of the formulas in the Full List other formula tab merely copy data from the Full List tab, and then wrap an IF statement around it to suppress blanks. Instead, put the Service Address # and Service Address Street columns directly into the Full List tab, and you will avoid 9 columns * 900 rows of formulas. That's 8100 formulas that Excel no longer needs to calculate.


Then read the 2nd bit, where I say:
Often I see thousands of unneeded formulas is when someone puts in many hundreds or thousands extra formulas to handle an input section that might contain any number of records. Sometimes these rows vastly actually outnumber the rows of data, because the spreadsheet designer put in such a large extra safety margin of formula rows ‘just-in-case’. And depending on what these formulas do, sometimes they get calculated often even though most of them don’t actually point to any valid input data, as in the case of the below screenshot where there are only 10 records on the left, but every formula in the section on the right is being calculated…with most of them simply returning #N/A.
And then go look at row 2132 of the NOV sheet. You have formulas way down there, but the actual input data finishes way up in row 368. So there's 38 columns * 1764 = 67,032 formulas that you can probably avoid, if you use Excel 2010's Excel Table functionality. Probably more, if you restructured your data layout. In fact, what you need to do is explain not what formula you want, but what you are trying to achieve with this file, because some things go from incredibly complicated to incredibly simple merey by you laying out your data better.
And then the same thing applies to the Dec tab.

Also if the user is to blame and not the tools then the first user to blame is MS developers that programed how Excel works since the tools to make it more user friendly is there.
In the case of your spreadsheet, that would be like me blaming Ferrari because I drove their nice car into a tree.
You need to start applying the advice that we are giving you.
 
Last edited:

Hui

Excel Ninja
Staff member
Harry0

A few things,

What Jeffery and Narrayan are saying is true
Excel can be super efficient or a sloth, and it is totally dependent on how we/you structure our models.
I have learnt techniques in Daniel Ferry's EHA course which took calculations from Minutes to Seconds and then to Milliseconds purely by structuring the data flow better as well as using advanced filtering/lookup techniques like Conjunctive Truth Tables

Unfortunately this will mean rebuilding a lot of your model
and that is probably work that only you can do as you know what your model does

Your upload File has expired from the upload site

Can you please re-upload it ?
 
Last edited:

bobhc

Excel Ninja
@jefferyweir

but my hourly rate for ad-hoc EXcel development is somewhere around $100US per hour.
Good god man at the rate you are progressing with the book we will have to mortgage the house to buy it....:mad:

@HarryO
Well can't say all of Ms products are best MS Paint vs Adobe Photoshop. lol
How can anyone with the slightest knowledge of software compare the quick on the go graphic manipulation (free colouring software), Paint with the full blown work tool for the professionals (expensive software for those who know what they are doing).Adobe??


.
 

Harry0

Member
I try to use the standard method of excel to do what I do quickly. Obviously there are better ways which again the user interface is limited. You have MS programs popping up many times giving you suggestions on basic things but not on more advanced things. so just like my last statement on programmers that made excel will get it one day to make it more user friendly to give helpful suggestions or recommended methods that would do the same things in a more efficient way. Just like how cars have advanced and not people so that you do not need a hand crank but starters to start the care eventually MS excel will break the mold one day unless google buys them out.

So the non standard way to remove 0 from a cell is to use the cell format 0;-0;;@ but the #n/a error comes up at times so an iferror function is needed. So I guess that is less cpu intensive than the If function.

why are you hurting vlookup's feelings when ms knew what they were doing. ;) vlookup... is the popular old standard choice. Also I was getting confused with the inconsistency of excel with how some formulas have the array 1st (index) while others have the array 2nd (vlookup/match). inconsistency is MS middle name sometimes to MIS. LOL

the 2132 rows is for future use when you is added since I do not want to go back editing the formulas again which you are right that the easier way is to put less rows but I do not want to keep editing it which it it comes to editing it might as well be to add 4000 rows if it reaches 2132.

I have an extensive knowledge of various programs and have to show laymen how to use it. I am a jack of all trades on everything and a master of none. I may know excel above average and a teacher use to call me to assist everyone in class how to use all of the MS office programs (ms office 97) in class years ago or I had to give her tips does not mean I am the best at it and I know how things are annoying and not use friendly to the masses. I get most of the basic and do from there and to be the master of one things then forget about it. All programs are inconsistent and the one that should be consistent is MS. There is a reason why some people get paid well for advanced things, and it is no like that programs like excel are not use friendly.
I am even using a Mac which is not my pc of choice which is annoying and limited in many ways. This thing does not have a shrink all button, the default settings are useless, does not have a standard delete button, its home button is useless, and the list foes on in how non efficient it is. It's excel clone called numbers is even worse in user interface than excel. Sure this Mac has some small improvements but it does not make it as efficient. Even the new MS windows throws out the good for useless things which can make the pc more of a mess, slow, and annoying. I even have done some projects to make things more user friendly. Even chess can be explained simply in a paragraph than how some people sell books for dummies as they call it which does not explain it as simple. Inefficiency costs companies money. I have another 5 other complex programs to deal with and 3 of them are the same programs which some have options the others do not in the accessibility they offer, while their official sites defend their bad user interface to even do simple things to rotate a 3d model object which one simple programs you right click and press rotate in the way you view it, while another you have to go to the menu bar, click edit, scroll down to modify, and click the clockwise or anti clockwise, while another is much longer in its process. Another to elevate a object you need to select the object and use on the side a bar or number to select how high you want it, while another you have to go click 4 options to get to move it and you can only type a number in how high you want it which you have to guess and keep doing it until you get it right which you have to select feet and inches. One programs you can do something in 3 seconds while another 3 minutes which the company states thats the only way it can be done and I have to learn to use it. Yet I am to blame for those bad Ferraris? So many bad programs and the ones that are top are the least messy, and never the be all end all since more updates come out to fix or update the previous versions.

It is annoying enough how this non apple keyboard with the delete key does not work like some other numbers on this. Or how excel messed up in the short cut keys. Many other mess ups in excel in what it glitches on and what it took out and I only commented on a few which I had to sometimes use the 10 years old version to do temporary certain things.

I will look into your other comments later and try to try them out.
 
Last edited:

Harry0

Member
Also index does not give an exact match which messed up everything so to need 2 formulas like "index" and "match" to do what "Vlookup" does does not seem intuitive. I say look for #40 and it gives me #400.
If you say not true well I get the wrong result with just index.

Unless functions are consistent to have the option to select exact match just like how the function "match" has that option.
 
Last edited:

GraH - Guido

Well-Known Member
Hi, jeffreyweir

After all this time (3 years?), you still haven't finished your book?
Otherwise I would have mentioned it to a few people who could benefit from such insights.
Or I just got fooled by your carefully set-up trap like any new one who gets tested by the cool kids... :oops:

Intuitively I've been saying to people they need to rethink their workbook build-up if things become slow. Often involving preparing data better, organizing data better, using better suited functions, removing formulas,... But I lack the back ground or in-depth knowledge to make it "hard".

So a book like yours could help many (if they are willing to listen/read). But will start by reading your post... If I can follow your quirky, lovely, weir(d) writing style. Which turns out a bit (very) difficult for me, a non-native English lingo user. Thx for all the sharing. I hope you are still active over here.
 
Top