Wanted: How YOU use Excel!

[TinaP]

True. But my point is, beginners to Excel don't fully understand the potential. At least once a week I come across people who use Excel daily, but can't write a formula. Many times, they put numbers in boxes then turn to their adding machines to calculate, then put the answer to their calculations in another box. Seeing how powerful Excel is goes a lot further than having someone say, "Spreadsheets are powerful tools used to analyze data." And showing them with a puzzle that they are familiar with can go a long way.

Sorry for hijacking the thread.

 

[sous2817]

Many times, they put numbers in boxes then turn to their adding machines to calculate

I spend the bulk of my days stopping the above. Also I help automate manual entry, compiling the data into something more "higher level", and showing my various managers that it isn't as bad as their bosses tell them it is (pretty graphs help!).

For what it's worth, I work in the healthcare industry in an area that's very metrics driven...

 

[Greg Truby]

Sous,

Thanks, can you give me some examples of some metrics that your roll up using Excel? Or what some of the manual entry processes are?

Tina - can you give a couple of specific examples?

Matt - thanks. I actually am familiar with the swine production sector, so I can totally relate to how you'd use it in poultry.

 

[Norie]

Tina

I really don't think that 1000+ lines of code, which is what is in the Sudoku solver/creator/whatever I'm referring to is a good start point for an Excel beginner.

 

[TinaP]

I had one person ask me to set up a table to track cash advances for each branch. I had her draw how she wanted the table to appear, which took her about 5 minutes as it was very basic and then we set about creating the table (borders, etc.). Then she started filling in the numbers for each branch and calculating on her adding machine. I can't remember the particulars, but she added two columns together, then subtracted the third column and placed the result in the fourth column.

I asked why she used the adding machine when she could use Excel, and I still remember the conversation:
"Why don't you just use Excel?"
"Because I don't know how to do it that way."
"I'm standing right here; why don't you ask me?"
"OK. Tina can you show me how to do this in Excel."

When I showed her how easy it was to write a (simple) formula. She looked at me and said, "That was easy." I couldn't deny it, and told her to call me any time she wanted to calculate something since I really didn't think she would remember it.

PM me if you would like copies of some of the generic files I've created.

 

[TinaP]

Norie,

You're right, it's not a good place for a beginner. I was thinking of the Sudoku Helper template that you can download from Office Online. Everything is calculated via formulas. It's certainly not flawless--I've found a number of (mostly cosmetic) problems, but I don't think I found any problems with the logic.

I challenged myself to improve upon it without using VBA and it's taught me a great deal about formulas, conditional formatting, etc.

 

[sous2817]

Sure...just to keep it somewhat blinded here is what people do here:

We're in the business of helping smaller business work as distribution points for a larger business. There's a lot of red tape involved so we work with the small business to get everything filed away. The large business wants to know how long it takes each small business to get all of their red tape completed and approved. So data entry is something like:

Excel Workbook
	A	B	C	D	E
1	Emp	Big Business Name	Small Business Name	Start Date	End Date
2	1	Data1	XXX	1/1/2009	3/6/2009
3	4	Data1	YYY	1/13/2009	3/3/2009
4	1	Data1	ZZZ	3/12/2009	4/19/2009
5	4	Data2	AAA	3/15/2009	6/6/2009
6	2	Data2	BBB	3/25/2009	6/22/2009
7	1	Data3	CCC	3/30/2009	5/5/2009
8	4	Data1	DDD	4/12/2009	6/21/2009
9	1	Data2	XXX	4/20/2009	5/29/2009
10	3	Data3	YYY	5/1/2009	7/28/2009
11	etc	etc	etc	etc	etc
Sheet1

Excel 2007

This is all put in manually as each small business process is started. Each employee wants to know if they've met their individual performance goal for bonuses / raises (# small business brought up, median times, etc). Their managers want to know how many each employee got up by month, quarter, year, their median times all by office number, etc. The managers' manager want to know overall how the department is doing (# up, median times, etc), and each big business wants to know how their individual project is doing, whether it's meeting timelines, deadlines, etc.

Before I was here, some poor soul was doing a lot of filtering / copying / and pasting to get each employee / office / big business in their own sheets and then calculating the metrics. The whole thing would break down if a manager's manager wanted to know how office 1 and 3 compared to office 2. The process was done once at the end of the year and would take about a month to sort through it all.

So using a few formulas / pivot tables / splash of VBA I'm able to split all this out in to different sheets so each entity can get their information when

 

[SydneyGeek]

My most recent Excel jobs have been:

1. Create 5-year forecasts for a large real estate developer. Each sheet was a different project; data was loaded to Access for consolidation, then pulled down to reporting workbooks (State and National)

2. Reporting workbooks using an OLAP system as the backend data source. Apart from upload and download, users could double-click a company name to hide or show detail for subsidiaries.

3. Pivot tables using an external database as the data source (Monthly reports). Users were able to view only their State's data. Updates were loaded from a third system each morning, and required extensive reformatting and 15-20 calculated columns before it could be used in the pivots. All the reformatting was driven using VBA.

Denis

 

[milkman]

I use excel to run production for a milk plant that produces 200,000 gallons of product a day.

I first created a weekly production program in excel that looks at Issues of each product (about 115 items)from previous week, subtracts inventory and tells me what I need to produce that day. I then created a daily workbook (template) that has twelve filler machine run scheduled and product code date, I pull what I need from weekly into daily workbook and print operators schedule report and they use it to produce product. Takes about an hour a day to run this.

I have worked at plant for 10 years and can have inventory down to 100 units or less on production of 30,000 before next run starts giving our customers the freshest product on the market. Not to mention the cooler space that is free and labor cost of transferring over stock product.

 

[snowblizz]

Want them to perk up? Spreadsheet horror stories!

"If you don't want to loose millions or billions like these guys, then listen up!"

If you can influence people to check their spreadsheets a little more thoroughly you'll probably have done a lot of good.