or Connect
Styleforum › Forums › Culture › Business, Careers & Education › Cool stuff you use MS Excel for?
New Posts  All Forums:Forum Nav:

Cool stuff you use MS Excel for? - Page 2

post #16 of 32
Quote:
Originally Posted by bringusingoodale View Post

Question to you guys using excel for heavy math stuff like algorithms and differential equations regressions etc:
Where did you learn to do this? I have no technical training and no science background. I am just curious, does an undergraduate degree in economics, business, engineering etc teach you to do this? It seems that few people I encounter use excel for little more than the basic crap. Even my nerdy friends don't use excel for complicated things.

I learned this stuff entirely on the job plus what I self-taught.
post #17 of 32
Good grief. How does one learn this on the job and self teach? I guess what I should of asked is what degree or training have you had that would enable you to even use Excel for such things.

Damn, I used to think I was smart in high school, then I used to think I was at least slightly above average intelligence in college, but now, ... now I think I am an average joe who likes to read and that's it.
post #18 of 32
Quote:
Originally Posted by bringusingoodale View Post

Good grief. How does one learn this on the job and self teach? I guess what I should of asked is what degree or training have you had that would enable you to even use Excel for such things.
Damn, I used to think I was smart in high school, then I used to think I was at least slightly above average intelligence in college, but now, ... now I think I am an average joe who likes to read and that's it.

Yeah, it is definitely learn it on the job and/or self-teach...because honestly, if someone had learned it in school...they would have learned not to use it in favor of R (if they went to school recently) or SAS/Stata/SPSS/etc. Instead, a lot of the excel knowledge comes from needing to do "one more thing" so you keep building on the knowledge.

My company doesn't have much in the way of training...but I am the kind of person who is always looking for a better way to do something and I learn pretty well by just googling shit. This has worked out well for me since the stuff I do at work can be extremely varied...taking an excel class would just try to teach me stuff I didn't care about and leave out the one feature I actually end up needing a month later. Some of the other companies in our field have more formalized excel training modules...while I would probably tear my hair out if I had to take them, I often interact with coworkers who would *really* benefit from them (the google-fu is not strong with them).

I just don't trust excel work as much....it is great for making attractive tables but once the calculations get complicated, it is incredibly difficult to verify accuracy. Obviously it is a great tool for building your DCF model...you can see all of the inputs and make adjustments to the parameters on the fly (and if you do it for a living, you are probably using pretty standard templates and formulas so strange bugs are unlikely) but every layer of complexity you add increases the difficulty of auditing the quality of your work. Right now I usually do all of my work in SAS (it is what we use and our data lives on a SAS server....R or anything else would be equally fine) so someone can just verify that my program works instead of verifying that every single function works...this has the added benefit of working regardless of changes to the input data. If I get another month's worth of data, my program shouldn't require any changes (other than telling it to include the data) whereas excel will all of the sudden end up needing formulas filled down and things copy-pasted around (and what happens when the new data is interspersed with old data...or some lines are added and others are removed).
Once the important calculations are made, I spit the data out into a CSV and import it to excel in an extra (can be hidden) tab. Then I create a pretty table on a new tab and use simple formulas to display the data (e.g =datatab!A2......=datatab!D25). Row and column totals or other simple bits are usually done in excel but any serious work beyond basic arithmetic is done with code from a statistics package.
post #19 of 32
Quote:
Some of the other companies in our field have more formalized excel training modules...while I would probably tear my hair out if I had to take them, I often interact with coworkers who would *really* benefit from them (the google-fu is not strong with them).

Ahh so there is industry or content specific Excel training. This eases some of my confusion, I figure not everyone can by a self-taught prodigy biggrin.gif

Seems like every time I get the idea that I want to learn how to do more complicated things in excel (nothing fancy really, think adding in values in money and showing how the depreciation would occur while taking into account some other factors...) I can't find decent tutorials for the self-teacher in me; it seems likes they are all either "hey lets learn how to make bold letters and resize columns" or are too advanced so that the intermediate steps are baffling. My ignorance is showing....
post #20 of 32
Quote:
Originally Posted by kekrre View Post

I can't imagine life without Excel. Almost all of my non-web time on my computer is spent on Excel. Without Excel, my computer would just be a toy. My field is chemical engineering and I often use Excel to solve a problem from start to finish because I can set up a problem and solve it in less time than it takes me to look up the relevant function(s), syntax, and write and debug a MatLab or Mathematica script.
Until my junior year of college, I didn't even know that you could add two cells together in Excel. I use it for almost every calculation now - even for stuff that I could do by hand, on a calculator or in a more complex math program like Octave, MatLab or Mathematica. I'm often amazed by how limited people think Excel is.
Some things I frequently do in Excel (all of these have very similar setup, fwiw):
- solve systems of differential equations describing reaction kinetics, heat/mass transfer, etc. I prefer using Excel to solve diffeqs with no analytical solution (rather than MatLab, Mathematica, etc.) because I can play around with the solver method, step size, etc. to see how the solution behaves. If the problem is implicit, I can set up a macro to iterate the solution to convergence.
- manually integrate graph data or discrete data points (usually for software whose freeware implementation only spits out a table of data with no integration work done on it), and manually differentiated curves.
- use Excel's random number generator to do searches for solutions to non-linear regression problems and generate joint confidence intervals
- use Excel's non-linear solvers to do robust linear regressions
- use Excel to explore how an algorithm behaves
I do 99% of my plotting in Excel and I've gotten really good at making graphs look professional and getting rid of that stock "Microsoft Office 2010 red blue yellow green" theme. Often, my diffeq solutions look 100% better than Mathematica or MatLab plots, so nobody ever complains about where it comes from.
I only really use Excel for math and I feel like I barely scratch the surface. With VB, Excel is essentially Turing-complete. I've never dabbled in forms (except on GDocs) and I rarely write my own formulas. I've never made a pivot table and the most complex "business-esque" data analysis thing I've done is dabble in GQL on Google Docs for my part-time boss's disgustingly disorganized spreadsheet-based job tracking system. I also rarely use built-in formulas, other than SUM, AVERAGE, STDEV and the linear regression functions, since I just plug in the formula for most of the math I'm doing.


I didn't think Excel was limited but you've opened my eyes.

I have Matlab on my home computer as one of my teachers gave a 10 week course on it and it's capabilities are immense and I just assumed Excel couldn't touch it.

 

Do you ever use Wolframalpha? I got the app on my iPhone, it's useful when you don't have access to anything that can help :)

post #21 of 32
Quote:
Originally Posted by Jorgeezy View Post



I didn't think Excel was limited but you've opened my eyes.


I have Matlab on my home computer as one of my teachers gave a 10 week course on it and it's capabilities are immense and I just assumed Excel couldn't touch it.

Do you ever use Wolframalpha? I got the app on my iPhone, it's useful when you don't have access to anything that can help smile.gif

Get Maple. If you're doing further maths the only things you'll be learning in the first term are set notation and Maple so learn those and you're done until January.
post #22 of 32
Quote:
Originally Posted by NameBack View Post

166
Note the scroll bar. This motherfucker ran up against the character limit on formulas.
Pretty much all the work I've been doing on my stock trading algorithm is done in Excel, except for the machine-learning/data-mining algorithms which my partner does in R. Although the results that R spits out have to be fed back into the backtester I've built in Excel to be deciphered. Whenever we build new models or tweaks to existing models we build 864 variations which all have to be tested, so I also use macros extensively.

Holy shit... and thought inputting stock options using if, then statements was bad enough... Can't you break up that forumla? Or if worse comes to worse, program in C?
post #23 of 32
Making a living.
post #24 of 32
I'm an economist and work in Resource Planning .. doing forecasting and trend analysis for a telecom.

I use Excel for:
1) Reporting. My bosses love pivot tables/charts. Giving someone a dataset, presented in a fancy table with a chart, and giving them the ability to change the graph .. has saved me a tonne of time, and headache explaining my work.

2) Impact Analysis. Like the above, building excel based models with a few simple manual entry fields that shows the impact to the business has been fun. I'm currently working on a resource plan for our retail stores that includes a feature that allows the user to input different staff levels and outputs the impacts on revenue/costs/profits/etc. A lot of the background work is done in R, but the front-end of the model is done in Excel.

What's great about excel is everyone (that I deal with in the work place) should know the basics. IE: How to input numbers and read results. This isn't the case for the more "sophisticated" statistical programs. To give my boss a model that is based in R, and expect him to use it .. well that simply wouldn't happen.

However .. I still do a lot of my more sophisticated modeling in R. I'm sure I could do it ALL in excel .. but that would require me to know exactly what I'm doing - something that is sometimes possible in theory .. I just don't have the time. My preferene for R over the others .. being able to read/write straight to MS Access has helped me integrate a lot of my R work straight into my Excel presentations.
post #25 of 32
Quote:
Originally Posted by bringusingoodale View Post

Question to you guys using excel for heavy math stuff like algorithms and differential equations regressions etc:
Where did you learn to do this? I have no technical training and no science background. I am just curious, does an undergraduate degree in economics, business, engineering etc teach you to do this? It seems that few people I encounter use excel for little more than the basic crap. Even my nerdy friends don't use excel for complicated things.

In my case .. no. Both my Undergraduate and Graduate training in Economics pretty much laughed at Excel.

Primary reason for this .. was NOT because "excel is limited" .. but because it is difficult to check one's work in excel. To manimulate a dataset, estimate a model, and present results in excel .. can require a lot of manual work. This is fine if you're doing the work for yourself .. but that isn't the case in academia. All your work needs to be replicated and double checked.

Then I got a job .. and everything I had to do was in excel.

What school trained me to do .. is to solve a given problem analytically. If you look at a problem as a series of equations .. then you can see how Excel can solve the problem. From there .. GOOGLE. If you know the terminology (ie: what the word "Regression" is) .. then doing google searches for "MS Excel Regression" is how I learned.
Edited by imschatz - 1/8/12 at 6:42pm
post #26 of 32
Quote:
Originally Posted by imschatz View Post

Quote:
Originally Posted by bringusingoodale View Post

Question to you guys using excel for heavy math stuff like algorithms and differential equations regressions etc:
Where did you learn to do this? I have no technical training and no science background. I am just curious, does an undergraduate degree in economics, business, engineering etc teach you to do this? It seems that few people I encounter use excel for little more than the basic crap. Even my nerdy friends don't use excel for complicated things.

In my case .. no. Both my Undergraduate and Graduate training in Economics pretty much laughed at Excel.

Then I got a job .. and everything I had to do was in excel.

What school trained me to do .. is to solve a given problem analytically. If you look at a problem as a series of equations .. then you can see how Excel can solve the problem. From there .. GOOGLE. If you know the terminology (ie: what the word "Regression" is) .. then doing google searches for "MS Excel Regression" is how I learned.

Yeah. in my undergrad degree for Economics, excel was not used -_-. The only excel used was the professors would have some hundreds of years of data in an excel file for you to then do regressions on with Stata.

Then step out into the real world with a shiny new expensive diploma, look on every job app
requirements

-MS Excel experience greatly preferred.

-x+ years experience with pivot tables, v formula lookups etc.

then it's like. oh shit.
post #27 of 32
Quote:
Originally Posted by imageWIS View Post

Holy shit... and thought inputting stock options using if, then statements was bad enough... Can't you break up that forumla? Or if worse comes to worse, program in C?

Unfortunately I don't know C or other real programming languages -- I bootstrap my way through life, pretty much. And that formula was the most efficient way I could think of to do what I was trying to do. It basically looks at four parameters, and according to those parameters constructs one of 48 possible formulas. It sort of takes the "building blocks" of the 48 formulas and stitches them together into a whole formula.
post #28 of 32
Quote:
Originally Posted by Sir Humphrey Appleby View Post


Get Maple. If you're doing further maths the only things you'll be learning in the first term are set notation and Maple so learn those and you're done until January.


I am doing Further Maths A2 level atm but don't start my degree until September/October but I will check this out, thanks!

post #29 of 32
Quote:
Originally Posted by CYstyle View Post


Yeah. in my undergrad degree for Economics, excel was not used -_-. The only excel used was the professors would have some hundreds of years of data in an excel file for you to then do regressions on with Stata.

Then step out into the real world with a shiny new expensive diploma, look on every job app
requirements

-MS Excel experience greatly preferred.

-x+ years experience with pivot tables, v formula lookups etc.

then it's like. oh shit.

I am not quite experiencing the same stepping out into real world scenario, but I am noticing that given my recent decisions I need to be competent with excel. The jobs I am talking about are not in the sciences so I don't need to learn advanced mathematical use, but I am not sure an employer would put up with skimpy excel skills.

I would appreciate any recommendations for online tutorials. I am having hard time finding an extensive tutorial where I can skip the really basic "welcome to computers" excel tips. FWIW,I am really a monkey see monkey do learner. I learned photoshop by spending a lot of my free time by going through a lot of youtube videos. I can't find the same experience with Excel, and I sort of understand why it has to be different given the application.
post #30 of 32
Quote:
Originally Posted by bringusingoodale View Post

I am not quite experiencing the same stepping out into real world scenario, but I am noticing that given my recent decisions I need to be competent with excel. The jobs I am talking about are not in the sciences so I don't need to learn advanced mathematical use, but I am not sure an employer would put up with skimpy excel skills.
I would appreciate any recommendations for online tutorials. I am having hard time finding an extensive tutorial where I can skip the really basic "welcome to computers" excel tips. FWIW,I am really a monkey see monkey do learner. I learned photoshop by spending a lot of my free time by going through a lot of youtube videos. I can't find the same experience with Excel, and I sort of understand why it has to be different given the application.

When I started my current job back in August the expectation was marginal excel skills. My boss gave us a day to play around on this website:
http://datapigtechnologies.com/ExcelMain.htm

There is lots in the "basics" but also gets into the more advanced stuff like Pivot Tables and VBA macros. Lots of cool videos, and it's based in 2003 so it makes you think about the problem a bit more as many of the features have changed (somewhat) since 2011. The basics are all the same though.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Business, Careers & Education
Styleforum › Forums › Culture › Business, Careers & Education › Cool stuff you use MS Excel for?