Needing Excel help, kind of.

[averygoodun42]

Excel and/or businessy-talented people, would you help me, please?

I have a table that I need to sort in order to "[d]isplay subtotals for sales by year, month, and territory." There's also a product category not mentioned, but when omitted makes for a very messy table.

How would you interpret that directive (with the assumption that it needs to remain one table)? There are two years, three territories, and five product categories. Would you make separate columns for the years (and maybe territories), then sort from there?

I know how to sort and subtotal; it's just I'm having a very difficult time visualizing what the final table should look like, and so having a difficult time figuring out what levels each section should go on.

Comments

[shiv5468.livejournal.com]

I'd do territory along one axis, and dates along another axis, and then subtotal for months, and then years along the date axis.

So it would be

territory 1 territory 2 territory 3 (two columns wide for each territory)

month 1
month 2
month 3....
month 12 and in the second column for the territory do the subtotals

[averygoodun.livejournal.com]

Mm. That would make sense...

I'll try it in case I can't use a pivot table. ;-)

Thank you!

[paisleysnail.livejournal.com]

Have you been given data or do you have to input it all yourself?

Pivot table function would auto total and sub total and you could play with the format until you were happy...assuming that the original data was input with the right categories/headings?

If you just have to make a table ... I would probably go months across the columns (so I could easily total the year in the last column). Then the rows going down would need to be arranged by territory, product then year, or territory, year then product...etc (may require more columns to split that out before moving down).

Hope that helps at all... that's my 5 min assessment of the situation. I'm sure there's better ways to do it.

[averygoodun.livejournal.com]

Fortunately, the data is given.

I definitely would prefer to use a pivot table (in fact, I've email the prof to see if using one in any capacity would be cheating) and have one already worked out. But I'm not sure it is allowed.

Thanks for your suggestions!