Advanced Excel tutorial suggestions

July 15, 2004 6:21 PM

ExcelFilter: I've got a specific problem in mind [inside for the curious/helpful], but more importantly I'm looking for an advanced tutorial for excel. I'm looking to go from someone who is confident with excel to being a full-on pro. Books are ok, but I'd prefer internet (i.e. free) resources. I am using Office v.X for Mac, but I think most techniques will translate pretty well across platforms. I'm looking for things like recursive formulas, modeling theory, little-known tools/techniques, pivot tables, etc.

Current problem: At a new job I've been handed a giant excel file with about 200 vending machines and their daily sales. there is a row of dates across the top, with today on the left and some date ~6 months ago at the far right. each machine has a row, and the sales for that machine for a given day is displayed under the appropriate date. got it visualized? now, i'm trying to calculate the average sales for each vending machine during its first week of operation. problem is that some were installed 6 months ago, some were installed yesterday. I'm pretty sure the key is finding an automated way to figure out the column number of the first cell from the far right that has a number in it, but nothing i've tried works. the only thing that almost worked was a recursive formula something like:
=IF(P8,COLUMN(P8),IF(O8,COLUMN(O8),IF(N8,COLUMN(N8),IF...
this works theoretically, but excel seems to only be able to handle 6 nested formulas (any more than that and it tells me that the formula contains an error, even though I know its correct), and i'm looking for something that will work across ~180 columns. is there some way to bypass this limit? any other ideas?

posted by rorycberger to Computers & Internet (16 answers total)

A visual basic macro would do all of that for you.
posted by the fire you left me at 7:03 PM on July 15, 2004

but I think most techniques will translate pretty well across platforms

Sorry this isn't an informative answer, but if you really want to go totally pro, you should question this assumption thoroughly. I've never found it to be particularly true.
posted by scarabic at 7:08 PM on July 15, 2004

A relatively quick solution, albeit a very ugly solution, would be to add two rows for each machine. In the row immediately below your daily sales, insert a formula that adds 1 to the value from the previous column in that row, but only if there is a non-zero value in the cell above it (e.g., there were sales made that day). In the row below that, add the identical formula to every cell: if row above equals seven, then average the previous 7 values from 2 rows up. Finally, you could hide these two rows for each machine and add a column by each machine name that shows the maximum value in the average row (this will give the first week 7-day average because the rest of the cells should have zero values).
posted by ajr at 7:11 PM on July 15, 2004

I think most techniques will translate pretty well across platforms. - rorycberger

I've never found it to be particularly true. - scarabic

Based on what experience(s)?
posted by BlueTrain at 7:35 PM on July 15, 2004

A variation on ajr's idea, that will keep you away from VB: for each row of sales, get two more rows: one row with the running total and one containing 1 or 0 depending on whether the running total for that month is 0 or not. The trick is to start calculating the running total from the far right (i.e. from your oldest data). The running total will switch to a permanent non-zero value at the first non-zero sales value (approx. your date of installation). The 1/0 switch will just tell you whether or not that particular month is before or after the first populated value.

To get the average of the months only after installation simply divide the most recent running total with the *sum* of your 1/0 row.

You're lucky 'cause this is the type of brainteaser that I solve for food :-)
posted by costas at 8:51 PM on July 15, 2004

Response by poster: Based on what experience(s)?

Looking through some of the older AskMe threads about excel, I guess I'm not as advanced as I thought. My experience consists of tons data entry and simple formulas, moderate amounts of charting and analysis, and a little bit of solver and some add-ins. For the question at hand, I'm looking for tips on creating complicated, nested functions which is more theory than interface, so it shouldn't be platform specific. I think I've decided that it's simpler to go through by hand and find the start date for each of the ~200 boxes, and just create a column with that in it. Once I have that nailed down, calculating sales for the first week, second week, etc. should be simple.

I'm still interested in good ways to teach myself more excel, if anyone knows any guides that go beyond SUM and AVERAGE. Visual Basic looks like something I should know a lot more about, anyone know any good guides on that? What exactly is lookup (and other related functions)? What are some other powerful tools/functions I should know?
posted by rorycberger at 10:51 PM on July 15, 2004

Response by poster: costas - I'm pretty sure that what you proposed can be accomplished with SUM/COUNT, e.g.
=SUM(A8:P8)/COUNT(A8:P8)

I still need a way to find the first day of sales (which is the installation date for our purposes). the one's and zero's might be helpful, but how do you automatically find the farthest right "1"?
posted by rorycberger at 11:04 PM on July 15, 2004

You should be able to find the first non-zero value using LOOKUP or VLOOKUP, and then work from that cell forward. You'd have to use some of the more technical functions such as CELLROW and ISEMPTY to get someething workable, though. It might be more productive to create a new worksheet that uses the LOOKUP result as its values, giving you straight columns of first-week results, and work from there. Good luck! I used to write Excel macros for *cough* fun.

LOOKUP, since you asked, is a way of finding a matching result in a table. Say you have

DAN    555
RORY   777
COSTAS 888

You could use something like LOOKUP(column, 888) to find the value COSTAS, or vice versa. It allows creation of crude database capabilities in a worksheet (actually, many internal business applications depend on doing just that).
posted by dhartung at 11:31 PM on July 15, 2004

This works assuming there are sales each day, =OFFSET(B2,0,COUNTBLANK(B2:F2)).

    
            Wk1     Wk2     Wk3
MACHINE1    $1      $1.50   $2.50
MACHINE2            $1.25   $2.75
MACHINE3    $0.7    $1.75   $3.50

It doesn't work on this though...

            Wk1       Wk2     Wk3
MACHINE1    $1       $1.50   $2.50
MACHINE2             $1.25   $2.75
MACHINE3    $0.7             $3.50

As for general Excel help pages, J-walk is very good.
posted by selton at 2:59 AM on July 16, 2004

Chip Pearson's site is very good.

You may also try adding a row above your titles and using the subtotal function (=subtotal(#,range) where # is the number to tell it to sum (9) or to average (1)). Then use the AutoFilter feature to filter the dates you want to calculate on. The subtotal function should automatically adjust to calculate whatever is visible in the range.

The other thing is to use Pivot Tables. They are your best friend.

In addition, Woody's Lounge is a good place to go to get help on specific questions or projects. Haven't been there for a while, but people were really helpful there in the past.
posted by evening at 5:05 AM on July 16, 2004

I still think a VB macro is your only hope (lookups only work with sorted data). I've tried emailing you a potential solution, but your address is wrong in your profile.
posted by BigCalm at 6:10 AM on July 16, 2004

rocycberger: be careful: count() will only give you the number of non-zero months. My method will give you the number of all months sinse the day of installation. I.e. count() will ignore months with zero sales since you installed a vending machine. If that's unlikely (from your description I thought not) then of course sum()/count() will be fine. As for how you find the first date of sales, a vlookup() or hlookup() using the 0/1 row as a switch should work (not sure what direction the lookups use first though)
posted by costas at 6:12 AM on July 16, 2004

Check out Mr. Excel and ExcelTip. Both are ad-heavy, but contain decent free info.
posted by jeb at 6:42 AM on July 16, 2004

I think most techniques will translate pretty well across platforms. - rorycberger

I've never found it to be particularly true. - scarabic

Based on what experience(s)?

I've used Excel more extensively on the PC, and tried several times to whip up something tasty on my Mac at home. It's about as annoying as any Mac/PC difference: just enough to slow you down, and certain things were never ported. Calculations are pretty much cool. Scripts and macros are implemented differently, keystrokes are all different, and certain things like database access modules have never been released for the Mac, or suck miserably. Yes, your calculations and things will transfer. It is much the same program. But considering that there are differences, I personally wouldn't use the Mac as my training platform if my goal were to learn Excel well enough to get paid big bucks for it out in the PC world.
posted by scarabic at 9:08 AM on July 16, 2004

It sounds like there are several possilbe solutions for your current excel problem. If you continue to push the excel envelope, you will eventually find limitations that Excel just can't take you beyond.

When you do run out of patience with excel, the next step is to install mysql on your mac, and then drive it with something like C or perl. Once you get into SQL, you have the following upgrade path:
move MySQL to a high end linux box, move the MySQL/Linux database to Oracel on a big unix box, move the Oracle database to DB2 on big IBM iron.

Just thinking about this process can provide insight into the techniques that larger competitors may be using.
posted by Kwantsar at 10:02 AM on July 16, 2004

Response by poster: Ok, so the stuff I'm working with actually starts out in SQL before I get it (in a daily email from the Software department) and put it into Excel. Any thoughts on teaching myself MySQL? Can I just get the software and a book and figure it out as I go along, or does it require any serious prior skills or knowledge? As I said, I'm new here so I don't really want to ask the software guys to teach it to me, but I am definitely interested in learning. And yeah, the more I use excel, the more limited I find it to be, so anything more powerful sounds very enticing.
posted by rorycberger at 2:34 PM on July 16, 2004