Thursday, September 29, 2005

New $50 Project - convert Word form into Excel

Thanks to John (from Texas) for his recent submission of a project.

John wants me to convert a business form that he had in Word into a form with automatically calculating fields in Excel. I busted it out pretty quick last night (and did a damn fine job, if I do say so myself, mirroring the layout, style, fonts, etc.) but have a few questions for John regarding the specifics of the calculations, etc., before I can complete it.

Man, it's been so long since Finance class for me...

The thing that is (still) boggling my mind is how to calculate the payment amount on a loan that has annual simple interest compounded monthly, if there are monthly (easy), annual (easy), quarterly (huh?), and semi-annual (huh?) payment plans. Anyone?

Anyway, I'm still trying to get back to do some fine-tuning for John (from Canada) on his golf league sheet, too...and the projects keep coming in. Don't think that I can't handle them, though. I'll get all the sleep I need when I'm dead!

Later,

Excle_Geek

Monday, September 26, 2005

I'm back from Mexico!

I made it back...barely.

Having to fly through Houston on Thursday when they were in the midst of canceling flight after flight was fun. We ended up jumping on standby on a flight to Oklahoma City since all of the Omaha flights had been canceled. (Anywhere north of here, you know!)

We made that flight then got on standby on a Southwest flight to Kansas City. Made that one, and rented a car in KC to drive home to Lincoln.

I'll dig through the emails for last week to see what all came in as far as $50 projects are concerned, and try to get them banged out tonight. I had zero access to the Internet in Mexico...which was at first scarey, then nice.

Time to get back in the swing of things.

Thursday, September 15, 2005

Oh, I forgot to tell you all that I'll be in Mexico for a week

Hey, I'm going to be in Mexico for about a week. I'll see you all again next Friday, so if you've got a project that is ultra urgent before then, I probably will have spotty access to the Internet from there, but I'll try.

If your needs aren't dire, keep firing away the $50 projects...right now I'm keeping up pretty good. I'm getting mostly same day turnaround, unless the projects are more involved and/or need more clarification on the submitter's end.

!Adios mis amigos!

Latest $50 project -- finding correlation between ad spending and revenues -- is done...

Edo recently submitted a request (Thanks, Edo!) for a means to analyze some advertising spending data and revenues over many periods, testing for correlation between each of multiple different advertising categories and the revenues.

Using =CORREL(), =HLOOKUP(), =MAX(), and =MIN() I set him up nicely with a solution that quickly and easily identifies top and bottom performers over the course of the entire time frame (cumulative for each period).

One should know that there's probably about a million other ways to go here.

Some other thoughts I had right off were to use the =RANK() function, both ranking ascending and descending, and then the =AVERAGE() function to average out the rankings over time to find consistent "winners" and "losers". Also, using simple conditional formatting, one could highlight ads that had particularly high (over a set parameter) or low (below a set parameter) correlations. And we haven't even left the use of the correlation statistic. There are many other statistics that return measures similar. If Edo was ambitious, maybe regression analysis is the way to go.

Regardless, here's another project done, and I didn't use a single line of VBA, but what the heck, it's another $50.

Wednesday, September 14, 2005

Just sent off John's golf league management project

John, thanks again for the project. I had a great time doing it, and I'll take a few more cracks and further integrating what I built with what you have, if you like.

To everyone else, what I basically accomplished for John was to create an easy, quick user interface to input the weekly results of a golf league. Using some fairly complex look-up referencing in Excel formulas, coupled with some VBA, I essentially turned one sheet of an excel workbook into a userform, and several others into various "tables". It's practically a database now!

Here's the interface:


What John gets is two tables -- one for individual historic performance, and one for team historic performance -- from which he can run all of his calculations to maintain handicaps, team and individual standings, as well as generate weekly results pages that he can export to the web.

Pretty slick if you ask me...and all for $50.

Tuesday, September 13, 2005

Update on the Excel Dashboard Kit - Quantity Discounts for companies

I just got a note from Charley Kyd over at ExcelUser, and they're rolling out a new quantity discount program, so that companies can get ahold of the kits for multiple users, and save substantially. Here's what she wrote:

"The Excel Dashboard Kit is licensed to a single user, not to an entire company. I recently received a request from a lady who wanted to purchase a quantity of kits. Here is the discount schedule I gave her:

Units Discount
2 = 20%
6 = 30%
10 = 40%
300 = 50%
500+ = 55%

These are approximiateldy the discounts that book publishers offer, except that they are slightly more generous for small quantities."

If your company is thinking about implementing a dashboard reporting system (or beefing up the one you have), be sure to check out Charley's prouducts.

People continue to take advantage of the $50 project deal

These keep coming in, and I'm steadily cranking them out...

A couple of the latest projects are from Brandy, who needed just a bit of help with some Excel Lab homework...

...and from John, who's looking to streamline the entry of golf scores into a golf league/handicap management spreadsheet that he uses to publish the results to a web page. Fun project, John, even though I'm not done yet. I was impressed by the publishing scripts in the file. Are they yours? This, of course, is right up my alley. Be sure to check out www.kolvensystems.com if you haven't yet.

More to come on other projects...E_G