DescentBB

Posted: **Sat Jul 07, 2007 8:35 pm**

I've been having to work in excel and so far it's been pretty great and i can make it almost do anything. There's tons of documents online for help and my personal life has gotten far more organized.

vba is going to be the next stage in the learning process. And i've already had to copy+paste some simple scripts for several projects for work.

Anyone here share the same love for this same app? And is it really better than any TI calculator out there (on a ti i've programed in vb to solve variables. im sure i could do the same for execl)?

Posted: **Sun Jul 08, 2007 12:35 am**

One of my undergraduate Math courses had a huge section on Algorithms, and we found that the best/easiest way to dynamically apply most of them was in Excel.

It didn't run as fast as writing it in C++, but it made dynamically changing the variables (boundary values, etc.) incredibly easy to handle, and displaying the results in simple-to-understand graphs was a breeze.

In my second job out of college, I ended up developing an organizational/data system in Excel to keep track of my inventory of stuff to handle. It impressed the manager so much, he sent it to everyone and told them to use it.

So, yeah, Excel (or more accurately, spreadsheet applications) rock.

Posted: **Sun Jul 08, 2007 5:04 am**

i was a big big fan of my TI82 because of how easy the vb was and the math i could do. And the jump to the 89 was great because of how complex i could get (and being bad at math 89 made me feel superhuman). This spread sheet app seem almost limitless to me. VBA looks like it might take a year to get good at.

Posted: **Sun Jul 08, 2007 4:15 pm**

It will take you more than a year.

Unless you really want the spreadsheet self-contained, learn regular VB, and just create excel files with the data you generate.

Posted: **Sun Jul 08, 2007 4:46 pm**

Testiculese wrote:It will take you more than a year.

Unless you really want the spreadsheet self-contained, learn regular VB, and just create excel files with the data you generate.

The project in front of me wouldn't be killing me if could just learn vba. I have a webstat report that has over 200 clients with different kinds of activity. I have one sheet with all the raw data. The other 7 pages is the same info but mixed around by company type (Tavel, computer purchase, auto deals, banking... lots of other junk...) and show exactly how it's being used. Right now im just sorting it all by hand because the computer can tell the difference between a fast food joint and a bank. But i want to make a script that can just grab names and stick em where they need to go no matter how the raw data is sorted from the admin webstats.

edit: ugh... almost done.

Posted: **Mon Jul 09, 2007 5:32 am**

You limit with spreadsheet applications: Calculus & formulas. I suppose you can hack a derivative, but you can't solve a derivative for an equation. Same goes for integrals, you might be able to get some numbers out of it, but you can't get it to spit out an equation for you.

Posted: **Mon Jul 09, 2007 9:17 am**

After taking accounting in high school, I set up a very detailed, debit/credit, multipage budget-tracking Excel spreadsheet that I used for many years. I also set up a grading system for my mother, a 4th-grade teacher. You can really accomplish lots of neat things with just the in-cell formulae, it's a shame most people just use it for the nice tabular format. I've seen people enter numbers AFTER they get those numbers from a calculator--they have no idea Excel can do the math for them!

Posted: **Mon Jul 09, 2007 12:05 pm**

snoopy wrote:You limit with spreadsheet applications: Calculus & formulas. I suppose you can hack a derivative, but you can't solve a derivative for an equation.

Right of the bat, you're correct. But if i can write the vb on a TI82 i bet you anything vba in excel can do it.

Sedwick wrote: After taking accounting in high school, I set up a very detailed, debit/credit, multipage budget-tracking Excel spreadsheet that I used for many years. I also set up a grading system for my mother, a 4th-grade teacher. You can really accomplish lots of neat things with just the in-cell formulae, it's a shame most people just use it for the nice tabular format. I've seen people enter numbers AFTER they get those numbers from a calculator--they have no idea Excel can do the math for them!

x2
I can't remember a thing and im bad with math. Excel and I are good friends. That sad thing is this program has been on all of our computers since it first came out. I've only noticed it until now.

Posted: **Mon Jul 09, 2007 3:59 pm**

Sedwick wrote:After taking accounting in high school, I set up a very detailed, debit/credit, multipage budget-tracking Excel spreadsheet that I used for many years. I also set up a grading system for my mother, a 4th-grade teacher. You can really accomplish lots of neat things with just the in-cell formulae, it's a shame most people just use it for the nice tabular format. I've seen people enter numbers AFTER they get those numbers from a calculator--they have no idea Excel can do the math for them!

After doing an internship in an accounting department, I am willing to go out on a limb and say that your spreadsheet was nothing compared to the spreadsheet I had to work with for a local not-for-profit. The sheer size of the spreadsheet made me cry, but it was fun to work with.

Posted: **Mon Jul 09, 2007 6:49 pm**

Zoop! wrote: After doing an internship in an accounting department, I am willing to go out on a limb and say that your spreadsheet was nothing compared to the spreadsheet I had to work with for a local not-for-profit.

I think vader said that to some one at some point in ep 5.

I crashed a 2 gig of ram dual core pc with a 250 sheet xls file. Thats when i found out the number of sheets is relative to the amount of ram your system has.

Zoop! what kind of machines did they have you slaving away on? And i know exactly how you feel about a large project getting done the right way. It's very satisfying.

edit:
Oh was he dead?
Welcome home son of dbb!

Posted: **Mon Jul 09, 2007 8:27 pm**

Welcome back from the dead, Zoop!

Posted: **Tue Jul 10, 2007 11:04 am**

Zoop! wrote:
Sedwick wrote:After taking accounting in high school, I set up a very detailed, debit/credit, multipage budget-tracking Excel spreadsheet that I used for many years.
After doing an internship in an accounting department, I am willing to go out on a limb and say that your spreadsheet was nothing compared to the spreadsheet I had to work with for a local not-for-profit...

You're probably right, but what the heck, I'll see if I can find it and PM it to you tonight, so you can look for yourself! I don't think there's any damaging personal info contained in it anyway.

Posted: **Tue Jul 10, 2007 11:34 am**

Sedwick wrote:
Zoop! wrote:
Sedwick wrote:After taking accounting in high school, I set up a very detailed, debit/credit, multipage budget-tracking Excel spreadsheet that I used for many years.
After doing an internship in an accounting department, I am willing to go out on a limb and say that your spreadsheet was nothing compared to the spreadsheet I had to work with for a local not-for-profit...
You're probably right, but what the heck, I'll see if I can find it and PM it to you tonight, so you can look for yourself! I don't think there's any damaging personal info contained in it anyway.

Are you using any vba?

Posted: **Tue Jul 10, 2007 12:35 pm**

The awesome thing about Excel is that in order to do even really complicated balance sheets and registers, you don't need a lick of VBA. People were tracking these things using Multi-Plan and Excel long before MS introduced VBA.

Posted: **Tue Jul 10, 2007 1:01 pm**

DCrazy wrote:The awesome thing about Excel is that in order to do even really complicated balance sheets and registers, you don't need a lick of VBA. People were tracking these things using Multi-Plan and Excel long before MS introduced VBA.

x2
i've been getting by great with functions. As far as they go I strongly recommend knowing =index(), match(), find(), and replace (). Those 4 in one cell are so powerful.

Oh and i'm learning how to make my own functions. Basiclly any function vba is like this:
=add(a1,a2) will just add two cell if writen like this.

Code: Select all

function add(cellone as double, celltwo as double)
add=cellone+celltwo
end if
End Function

Big new world i just stepped into.

Posted: **Tue Jul 10, 2007 1:06 pm**

Isaac wrote:
Sedwick wrote:
Zoop! wrote:
Sedwick wrote:<stuff about my budget />
<stuff about his internship in an accounting department />
<my offer to PM him my budget />
Are you using any vba?

Like DCrazy said, not a lick. Just formulae. Truth be told, the budget probably didn't have too much complex formulae. Now the grade tracker, that let you enter a case-insensitive letter grade with + or - and used a logical formula to factor it into a numerical average. That made for some full cells! Looking back, I could have simplified it, but hey, it did its job.

Posted: **Wed Jul 11, 2007 6:50 am**

Excel is basically a very low-powered visual programming language... but that said it's enormously powerful for most people given its ease of use.

Of course, after having dealt with a lot of Java, C/C++, C# and PHP in recent years, it doesn't impress me so much any more... but those are well out of reach of the non-dedicated.

Posted: **Wed Jul 11, 2007 10:38 am**

sirus wrote:Excel is basically a very low-powered visual programming language... but that said it's enormously powerful for most people given its ease of use.

x2
It's funny how primitive tool can make life so much better.

Posted: **Mon Jul 16, 2007 1:22 am**

Haven't been exactly dead, just easily lost and confused by lots of shiny objects.

The machine was a standard computer--I don't remember anything specific (I don't work there anymore). There was no VBA or anything, it was just a spreadsheet someone made at one point and it kind of exploded. It's still very unmanageable and not very user-friendly. It got the job done--I wish I could have redone it, but working with it was exciting enough. The budget spreadsheet was great for simply having it in a spreadsheet, but importing it into the accounting system took a couple weeks.

Oh well, all hail spreadsheets.

Posted: **Mon Jul 16, 2007 6:17 am**

DCrazy wrote:People were tracking these things using Multi-Plan and Excel long before MS introduced VBA.

Multi-Plan ...

*sniff* *sniff* ah, the memories!

(I wish I still had them

)

Posted: **Mon Jul 16, 2007 9:39 am**

dissent wrote:
DCrazy wrote:People were tracking these things using Multi-Plan and Excel long before MS introduced VBA.
Multi-Plan ...

*sniff* *sniff* ah, the memories!

(I wish I still had them )

Im guessing multi plan was replaced by lotus notes?

Posted: **Thu Jul 19, 2007 12:35 pm**

Isaac wrote:Im guessing multi plan was replaced by lotus notes?

The Lotus spreadsheet application was called Lotus 1-2-3, as I recall.

Posted: **Thu Jul 19, 2007 2:11 pm**

Zoop! wrote:
Sedwick wrote:After taking accounting in high school, I set up a very detailed, debit/credit, multipage budget-tracking Excel spreadsheet that I used for many years. I also set up a grading system for my mother, a 4th-grade teacher. You can really accomplish lots of neat things with just the in-cell formulae, it's a shame most people just use it for the nice tabular format. I've seen people enter numbers AFTER they get those numbers from a calculator--they have no idea Excel can do the math for them!
After doing an internship in an accounting department, I am willing to go out on a limb and say that your spreadsheet was nothing compared to the spreadsheet I had to work with for a local not-for-profit. The sheer size of the spreadsheet made me cry, but it was fun to work with.

Then I'm sure you're glad to hear that as of Excel 2007, there can now be over 1 million rows and 16k columns.

Edit: I meant 16k columns.

Posted: **Thu Jul 19, 2007 9:04 pm**

Isaac wrote:Im guessing multi plan was replaced by lotus notes?

Multiplan was the original name for Microsoft Excel. It had its ass kicked by Lotus 1-2-3 on DOS and VisiCalc on the Apple II, but was the first GUI spreadsheet app for the Mac.

Topher, whom can I kidnap to force more information out about Office:mac 2008?

Update your friggin blog already!

Posted: **Thu Jul 19, 2007 9:51 pm**

so...

after reading a bunch on vba i found out if im a newb still, i can plug regular formulas into my module! So now im using vba editor at work

im kicking so much 4$$

Posted: **Thu Jul 19, 2007 11:30 pm**

DCrazy wrote: Topher, whom can I kidnap to force more information out about Office:mac 2008?

Update your friggin blog already!

http://en.wikipedia.org/wiki/Office_2008

And I was on vacation for the last two weeks!

Posted: **Fri Jul 20, 2007 12:11 am**

Topher wrote: Then I'm sure you're glad to hear that as of Excel 2007, there can now be over 1 million rows and 65k columns.

only 65k rows? you can do better than that.

Posted: **Fri Jul 20, 2007 12:27 am**

fliptw wrote:only 65k rows? you can do better than that.

No, this is a physical limit of computing. Any more numbers would mean spreadsheets would now be wider than the universe is, requiring universe extentions which is currently patented by the same folks who invented the universal remote.

Posted: **Fri Jul 20, 2007 6:59 am**

haha

Seriously though, has anyone ever needed close to the width/height Excel spreadsheets give you? Back when they were \"only\" 256 columns by 65536 rows it might have been easier, but I can't think of a usable spreadsheet that could conceivably hit the current limits...

Posted: **Fri Jul 20, 2007 8:40 am**

Back in the 256-column days, I remember having to work on a spreadsheet where the creator had set it up so each \"record\" would be in a new column, instead of a new row. Yeah, that was frustrating.

Posted: **Fri Jul 20, 2007 8:58 am**

You would need a drafting printer for that many rows. Who uses such a thing? All huge reports i've ever done were consolidated into graphs and broken by sheets.

Posted: **Fri Jul 20, 2007 10:05 am**

The one I worked on wasn't printable, but it was up to column JJ or something. It was a total waste, it really belonged in a database.

Posted: **Fri Jul 20, 2007 10:53 am**

Rest assured, people do use the extra space and hit the new limits.

Posted: **Fri Jul 20, 2007 11:01 am**

Topher wrote:Rest assured, people do use the extra space and hit the new limits.

I guess if you print to a large drafting sheet and include everything from graphs, tables, sheets, all on one page, it would make a good presentation chart. But still at our office we just put everything on the projector in the conference room. Same deal.

edit:
Very cool thing just happened at work right now. The insurance expert who works next to me had a problem and needed a special list made from a large table of almost two thousand lines of insurance customer information. God i love formulas!!! The 3 formulas i
wrote quickly looked at all the zip codes, sorted them into areas, then counted the areas spitting out the top 3 areas with a list of the individual zip codes (not repteted which made a short list). I was a hero when i gave this report back in under five minutes.

Formulas used:
countif(), replace(len()), if(e2=e3,"",e3)

DescentBB

So no one told me about this excel program being so good.

So no one told me about this excel program being so good.

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re:

Re: