Christine -
Here's one reply to your question. First, if you are comfortable using a relational database like Access or Paradox, they are better designed for this nested calculations than is a spreadsheet like Excel. That said, here's what I do with Excel - it is pretty "primitive", so hopefully you will get better solutions from others.
I am assuming you have a column in which there is a NISP value for each line of data.
1) be sure to click the upper left corner of the spreadsheet so that everything is selected
2) use Data, Sort to sort the selected dataset (if you are using an older version of Excel, you may be limited in how many nested sorts you can do; the newest version lets you add lots of them)
3) here comes the primitive part:
- go down your database and insert 2 blank rows between each of the nested categories you wish to sum
- go the blank cell at the bottom of the column you wish to sum and use the summing icon (looks like a capital M tilted over on its side)
- double check that it is summing the range of cells you want summed (it will choose automatically, usually stopping at the first blank or non-numerical cell)
- when you click the summing icon it sums that column for you
- I often take the time to bold the sum so I can find it quickly later
- repeat until you've got sums for all the nested groups you want
- early in the process, double check a couple of your sums to make sure they are coming out as you intend
4) I usually save that version of the database to have a record of what was summed
5) I usually copy my summed values over to the next sheet to have greater ease in analyzing them
Just one way to approach it. I use this when teaching classes for which there is not time to teach the students relational database software.
- Jean
Jean Hudson
Associate Professor, Anthropology
University of Wisconsin - Milwaukee
----- Original Message -----
From: "Christine Kendrick" <[log in to unmask]>
To: [log in to unmask]
Sent: Tuesday, May 3, 2011 9:56:09 AM
Subject: Producing NISP with Excel Formula
Dear Zooarchers,
I have a basic question regarding excel. It seems like it should be a simple matter to produce a NISP for a given species from an excel data sheet. I have tried a couple of different functions, such as COUNT, DCOUNT, COUNTIF; I suspect that I am producing an error in the nesting functions. Have any of you ever used Excel to do to a NISP? Would you be willing to provide me with instructions or an example? Any assistance is greatly appreciated!
Cheers,
Christine
|