Hi. this is the alternative that I have long used. It produces ready to publish tables. But, you have to make sure that you click on "sum" not "counts". Counts calculate the number of lines in the sheet (cases), while sum calculates what you really want.
Best
Haskel
-----Original Message-----
From: Analysis of animal remains from archaeological sites [mailto:[log in to unmask]] On Behalf Of Sarah Whitcher Kansa
Sent: May-03-11 10:49 AM
To: [log in to unmask]
Subject: Re: [ZOOARCH] Producing NISP with Excel Formula
Hi Christine,
Two functions you may find useful in Excel are "pivot table" and
"filter." Create a test spreadsheet and play around with these two
functions to see how they work.
Pivot tables, in particular, are very good at summarizing your data
without having to manipulate the original spreadsheet. Start by
highlighting the data in the worksheet, then click on Insert and then
Pivot Table. Choose to open the pivot table in a new worksheet. Once
there, you will be able to pull field names into the row and column
areas of the table.
Good luck!
Sarah
On 5/3/2011 8:35 AM, Jean L Hudson wrote:
> 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
>
--
Sarah Whitcher Kansa
Executive Director, AAI
Editor, Open Context
www.alexandriaarchive.org
www.opencontext.org
Tel: 1-415-425-7381
Fax: 1-866-505-8626
|