Excel ergonomy - How do I set up my database?

[dourpil]

Hi !

I've been wrapping my head around that issue for several hours and I can't seem to figure it out...


I'm torn between a very elegant setup (supereasy data analysis, superslow data entry), an elegant setup (easy data analysis, slow data entry) and a very ugly setup (fast data entry, painful data analysis). I hope there is a 4th option


In short: every day several items are tested. There are ~45 different items and each item receives two types of tests (A and B).
Let's call items I and types of test T
Items formats are for example: 6-435 ; 6-435 ; HTHV04032 ; .....
Results of Types of test are numbers (from 0 to ~400)



The very ugly setup with fast data entry is this one (column names):
Date - I1 TA - I1 TB - I2 TA - I2 TB - I3 TA - I3 TB - ............

Which is easy to navigate within (data entry can be made with arrows and numkeys alone, 1 date can contain several results, and you can have a paper form that matches the order in which columns are set up) but:
- really painful for calculations (e.g average results for type A will make me refer to a lot of columns)
- gives a super-large worksheet because of the item names and the need of having 2 columns per item.

The elegant setup would be (column names):
Date - TA - TB - I1 - I2 - I3 - I4 - I5 - .......................

Where you'd e.g put an "X" in the item that corresponds to the results.
This would make me need half the columns and make calculations easier but:
- Not very fast data entry: date has now to be entered everytime and.. well that alone is a big issue

The very elegant setup would be (column names):
Date - TA - TB - I

Where I is a data validation list and you have to find your item in there.
That makes calculations a piece of cake but slows even more data entry.



Any suggestion?

[Bernie Deitrick]

I would use, at a minimum, 4 data fields

Date Item Test Test Value

For fast data entry, you could use a userform with a calendar control for Date, a list boxes populated with valid entries for the Item values, radio buttons on a frame for the Test, and a textbox for the Test Value - and code to place the new values at the bottom of the data set.

For data analysis, the table would work very well with a pivot table.

Other information that you could record would be things like the name of the tech or other specific info that could impact the results.