Excel Layout Problem

[kevmeister123]

Hello there,

I want to know if there is a better way to lay out my friends Excel Spreadsheet table as it's difficult to make formulas from it.

At present it looks like this:

Customer | Sales Week1 | Profit Week1 | Sales Week2 | Profit Week2 .... Sales Week51 | Profit Week51 |

Marrisons
Osda
Pesco's
Jainsburys
Hafeways

So to make a total of the Weekly sales I have to go =(A2+A4+A6+A8) etc etc
and same again if I want a total for Weekly Profits.

I could do 2 seperate tables one for sales, then another for profit, but it is very useful to see each weeks profit next to it's corresponding sales figure.

Is there a better way?

Thanks

Kevin

 

[xlbo]

Customer | Week | Sales | Profit

Then you can sum it all in a pivot table in about 5 seconds

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlhelp]

Or, if you are a real die hard fan of the table you have then

=SUMPRODUCT((MOD(COLUMN(B2:J2),1)=0)*B2:J2)

This only takes into account columns B to J and yuo may need to adjust the formula for the sum you are looking for.

Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

Sorry, I copied the wrong fromula, it should be

=SUMPRODUCT((MOD(COLUMN(B2:J2),2)=0)*B2:J2)

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Kevin,

I'd strongly suggest you go with Geoff's initial suggestion (especially since you asked if there is a better layout). If you have normalized data your life will be much, much easier going forward.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[kevmeister123]

Hi Peeps, thanks everyone for their help, that what I was after normalization (I couldn't think of the word earlier).

The only trouble with Geoff's layout is would I have to now have 52 rows PER customer.

Customer | Week | Sales | Profit

Osda | 1 | £500 | £20
Osda | 2 | £300 | £10
Osda | 3 | £350 | £15


So if I have 100 customers, it would mean 5200 rows?

Is this me being stupid? (forgive me if it is)

Thanks again

Kev

 

[xlbo]

nope - you are correct - 5200 rows for 10 customers (if they order every week) - not very big for excel (65536 rows). This will allow you to accomodate apprx 1200 customers. More than this and you should port over to access....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlhelp]

What Geoff has suggested is easy to manage. Yes you would have 5200 rows. The advantage is that you wouldn't need to find a customer or the week you are in; you would just add the info as you go along. Your sreadsheet will grow and you would be able to manage with very little manipulation.

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Keep in mind: even though the table might be 5200 rows, you (or management) don't have to look at the whole table to try to see what's going on. With your data in this format you have lots of easy and fast options for how to see the data.

-For starters, you can just use AutoFilter to see the info for a particular customer.

-Charting (graphing) will also be easy with the data in this format.

-As Geoff mentioned earlier, pivot tables will allow you to quickly and easily parse the data.

A couple of further suggestions:
- Use a different format for the week, something like 2006-01, 2006-02. Or, better yet, use the start-date or end-date for each week. After all, if everything goes well with your friend's business he will want to keep adding to the table next year. NOTE: You should not break up each year into its own sheet.
- If you are going to have a lot of customers, consider adding a customer ID field. Each customer should be assigned a unique ID - maybe a number (0001) or three letter abbreviation (OSD).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MallardVHS]

Hi Kev,

I agree with nearly all of the above. Just one thing - your situation BEGS for a database solution such as MS Access (no, I don't work for them ).

While it can be done in Excel, anytime you keep having to repeat data, you should think "database." One table for the static data such as customer name, ID, address (stuff that doesn't change) and another table for dynamic data such as weekly sales. Yes, you can still do computation, summaries, graphs, etc. in Access.

I realize this only helps if you are familiar with and comfortable with Access. If you aren't, use the ideas posted above to help solve your issues. When you get a chance, however, try your hand at Access. Think of it as Excel's cousin, which, while younger, is nonetheless more mature and worldly.

Best of luck
don

[green]Tis far easier to keep your duck in a row if you just have the one.[/green]

 

[xlbo]

Don - "more mature and worldly" - sorry but can't agree. What it IS is a database which excel isn't - so it is better at database things than excel (once you get above a certain size). Excel was developed as a spreadsheet - it just happens to be used as a single table database quite frequently - wonder how mature and worldly access is for multiple layer regression or financial calculation algorithms.......

For a few thousand records, there is no need to port over to access - for more than that - yes, it is a good tool but if you are getting serious, I would bypass that altogether and use SQL Server....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MallardVHS]

Geoff,

I certainly meant no disrespect to Excel. I love that program, and even use it more than Access. Access is a bear to learn, after all.

My reply was based on the OP's circumstance/problem. Not the number of records. Access can deal with low data volume as well as Excel can.

Without question, Excel can handle many of the same data issues as Access. That doesn't make it the best solution.

Hey, two awesome programs. Choosing the right one is the ticket!

Don

[green]Tis far easier to keep your duck in a row if you just have the one.[/green]

 

[CBasicAsslember]

MallardVHS,

Hey, two awesome programs. Choosing the right one is the ticket!

Why would you need to choose one? Why not use both? Use Access to store your data and Excel to extract, manipulate, and report.

 

[xlbo]

lol - some good points but as a starter for 10 I would use excel and then port upwards as the user gets more familiar with database design....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MallardVHS]

Hey folks,

Excel is enormously powerful and as twisty as a pretzel (that was supposed to be a compliment ). It's also very intuitive, making it far easier to learn (IMHO) than many programs.

Access takes far more effort t learn. It's about as intuitive as, as, hell, I don't know - insert some completely non-intuitive thing or other.

CBasicAssembler - I didn't suggest using both because I thought the OP might not know Access. Your idea is terriffic, of course.

Best of all good things!
Don

[green]Tis far easier to keep your duck in a row if you just have the one.[/green]