Tuesday, October 6, 2009

What’s wrong with this spreadsheet?

Working with the Suspicious Subtotal worksheet in Chapter 8 on page 257 our group noticed the following:

  1. When the person originally was sorting the spreadsheet they had sorted the totals by each specific item, then by category, then by month. This added about 1900 rows to the spread sheet that weren't needed because the information wasn't sorted correctly. What we did was just the opposite sorting by month, category and then item.
  2. When working with the Subtotal function we just wanted to see the main subtotals for the months, then break down into category, and then by item. When doing subtotals you need to not replace subtotals when you are doing a break down, meaning you just want the main subtotal to be for the Month, and then you want your own subtotal for category, and item. If you select replace subtotal in the design box in excel it will change your information so that it will be incorrect.

5 comments:

Kevin said...

Computers are surprisingly stupid. One thing like a sort makes everything the follows off.

QT said...

Our spreadsheet had a problem with data source, but it sounds like yours had a problem with data sorting. I'm glad we didn't have to fix something with both of these problems combined.

Spencer said...

I learned that you need to make sure you have all the information in the right order or it won't make sense to the rest of the columns.

Anonymous said...

This was a great exercise to learn in a group. We actually did another exercise to understand what we needed to do in this one.

Brian said...

One helpful thing we learned was when you're trying to sort by month, specify a custom sort so it sorts it by the actual month order. Otherwise, it will sort it alphabetically. Overall, though, it was a good exercise in teamwork and cooperative problem solving.