[Verbiage]

That's what it looks like to me, too. Looks like he disappeared though. Lol.

[BMW F22]

I'm here. Just came back from an event. Been bouncing back and forth on some projects. What I am trying to do is just automate the process a little. It's not urgent so I'm putting it on the back burner for now. I will try it again when the more important tasks are done.

[M3 Number 86]

Originally Posted by BMW F22

I'm here. Just came back from an event. Been bouncing back and forth on some projects. What I am trying to do is just automate the process a little. It's not urgent so I'm putting it on the back burner for now. I will try it again when the more important tasks are done.

what i posted should do the trick in terms of all the calculations. if there are other things you need to automate which does not concern data will probably need to do macros.

but the example above should be put in new data dump. done.

[Verbiage]

Originally Posted by BMW F22

I'm here. Just came back from an event. Been bouncing back and forth on some projects. What I am trying to do is just automate the process a little. It's not urgent so I'm putting it on the back burner for now. I will try it again when the more important tasks are done.

Better take advantage of this now, M3 Number 86 starts his new job soon and won't have time to teach class for awhile.

[BMW F22]

Originally Posted by Verbiage

Better take advantage of this now, M3 Number 86 starts his new job soon and won't have time to teach class for awhile.

Lol. I bought that GS Groupon class so I will be watching those videos to pick up whatever else I can. While I think that I don't need to be the best of the best on Excel, I would like to be very good at it. Whether I need it to be where I want to be the future doesn't matter. I'm always up for learning new skills.

[Verbiage]

Originally Posted by BMW F22

Lol. I bought that GS Groupon class so I will be watching those videos to pick up whatever else I can. While I think that I don't need to be the best of the best on Excel, I would like to be very good at it. Whether I need it to be where I want to be the future doesn't matter. I'm always up for learning new skills.

That's the way to do it. I feel the same way. I hope you can get some pointers from the GS course. It definitely helped me a lot.

[M3 Number 86]

Originally Posted by Verbiage

Better take advantage of this now, M3 Number 86 starts his new job soon and won't have time to teach class for awhile.

Lol my new I have to build everything from scratch as we go.

[BMW F22]

Got a question: been having issues with Excel 2016 running really slow, any fixes? Basically when clearing filters or doing anything it takes forever to run. Tried switching from auto to manual calculation and nothing.

[fecurtis]

Originally Posted by BMW F22

Got a question: been having issues with Excel 2016 running really slow, any fixes? Basically when clearing filters or doing anything it takes forever to run. Tried switching from auto to manual calculation and nothing.

How large is the file? Got any external links?

If you've got hundreds of thousands rows and what not, there isn't really much you can do.

[BMW F22]

Originally Posted by fecurtis

How large is the file? Got any external links?

If you've got hundreds of thousands rows and what not, there isn't really much you can do.

It's about 50,000 rows and counting. Funny thing is that the file my coworker sent me was much quicker. I looked and saw that she was on 2010 so I installed 2010 and voila, super fast (though functions wise not as good as 2016). Even on 2013 it was really slow. I'm not talking just slow, but on 2016 and 2013, it would give me the running circle and freeze (screen goes white) while it's calculating. Then maybe 5-10 mins later it would be okay.

[fecurtis]

Originally Posted by BMW F22

It's about 50,000 rows and counting. Funny thing is that the file my coworker sent me was much quicker. I looked and saw that she was on 2010 so I installed 2010 and voila, super fast (though functions wise not as good as 2016). Even on 2013 it was really slow. I'm not talking just slow, but on 2016 and 2013, it would give me the running circle and freeze (screen goes white) while it's calculating. Then maybe 5-10 mins later it would be okay.

2016 is a memory draining piece of shit, so that'd probably explain why.

IIRC, 2013 was a bit rough too.

[Hawkeye]

Rows are more efficient than columns so try and always keep columns to a minimum. Having no external links and having formulas not link to the right helps as well if you have a lot of data (50k rows is not that much, I am usually around 800k-1m rows).

File type helps a lot too for size and speed - if you are not already I would suggest using .xlsb

[BMW F22]

Originally Posted by Hawkeye

Rows are more efficient than columns so try and always keep columns to a minimum. Having no external links and having formulas not link to the right helps as well if you have a lot of data (50k rows is not that much, I am usually around 800k-1m rows).

File type helps a lot too for size and speed - if you are not already I would suggest using .xlsb

Are you using 2016 or different version? I only have about 10 columns. Will try xlsb. Thanks!

[Hawkeye]

I am using 2016 - My company is pretty quick to conform to the most updated software (for better or worse)

If you do have any excel or VBA questions feel free to tag me in the post and I can take a look. I've been using Excel pretty heavily for over 10 years now.

[BMW F22]

Originally Posted by Hawkeye

I am using 2016 - My company is pretty quick to conform to the most updated software (for better or worse)

If you do have any excel or VBA questions feel free to tag me in the post and I can take a look. I've been using Excel pretty heavily for over 10 years now.

Schweet! Thanks!

[pgviper]

Use R & Shiny. No need for excel.

Also, learn to format your data properly as well.
https://cran.r-project.org/web/packa...tidy-data.html

[Hawkeye]

Originally Posted by pgviper

Use R & Shiny. No need for excel.

Also, learn to format your data properly as well.
https://cran.r-project.org/web/packa...tidy-data.html

While great for students and statisticians - it is not incredibly useful or practical to implement into practice for most companies.

[fecurtis]

Originally Posted by pgviper

Use R & Shiny. No need for excel.

Also, learn to format your data properly as well.
https://cran.r-project.org/web/packa...tidy-data.html

Do you have any other really, really impractical solutions?

[pgviper]

Originally Posted by Hawkeye

While great for students and statisticians - it is not incredibly useful or practical to implement into practice for most companies.

Why do you say that? The primary draw back is that it has a higher learning curve than basic excel.
But at the same time, your time is much better spent learning R than diving into macros and creating solutions to problems that can be done in a few lines of R.
Those same lines can then be automated and built upon as opposed to excel.

Originally Posted by fecurtis

Do you have any other really, really impractical solutions?

Here we go...

And why do you think Excel is so much more qualified than R for moderate to advanced reporting?

You can then make connections to your data sources for NRT reporting to a dashboard using Shiny.
You can also take your analysis (tables, graphs, etc...) and put directly into a markdown.

So again, please tell me how a costly, limited enterprise product is the "practical" solution when compared to a free, open source tool with significantly more features.

[fecurtis]

Originally Posted by pgviper

Here we go...

And why do you think Excel is so much more qualified than R for moderate to advanced reporting?

It isn't, but no one in an actual corporate environment uses it so it's impractical.

Your typical accountant, executive, etc all know how to use Excel.

From a practical standpoint for more advanced analysis using incredibly large data sets, we just use SQL anyway.

[pgviper]

Originally Posted by fecurtis

It isn't, but no one in an actual corporate environment uses it so it's impractical.

Your typical accountant, executive, etc all know how to use Excel.

From a practical standpoint for more advanced analysis using incredibly large data sets, we just use SQL anyway.

So just because something isn't mainstream means its impractical?

SQL is an aggregator and transformation tool. When paired with Tableau or some type of dashboard, yes its great! But we are talking about local exploratory work. When it comes to EDA, why use excel over R. There is no benefit.

[fecurtis]

Originally Posted by pgviper

So just because something isn't mainstream means its impractical?

Yes exactly, in the real world no one is going to take the time to learn something new when they don't see the value in it. Especially since 99% of what they likely do can be done in Excel.

SQL is an aggregator and transformation tool. When paired with Tableau or some type of dashboard, yes its great! But we are talking about local exploratory work. When it comes to EDA, why use excel over R. There is no benefit.

Because, unless you're working with a lot of data, Excel is suitable for 99% of most of what you'd see in the corporate world

Now if we're talking about scientific or statistical research, I'd imagine they're much more likely to know and use R since yes, Excel would be horrid to use at that. Hell I hate even using it for something as simple as regression analysis.

I know I haven't used it since college.