Calc. field/item

[tripptofer]

Hiya-

I'm trying to get my head around calc. fields and items as I am certain it would make my life easier.

Attached is a book with data and a mock pivot table.

I would like to be able to add a chitest column comparing males and females (data present in table), but would also like to know how to drag in other fields not explicitly displayed eg, percent deceased by gender (status=deceased).

Cheers in advance,
tripp

 

[Trevor G]

Tripp,

I have looked at your excel spreadsheet, but no exactly sure what you are trying to achieve and even if a Pivot Table is the right function.

Would you consider having something like a summary sheet which breaks things down where you can use countif function and a number of others.

If this is acceptable would you put together a summary sheet detailing your requirements, so headings and other titles.

Would be useful to know also the version of MS Excel you are using as you may have a few more functions that could be used, or another method to develop a solution for you.

Look forward to hearing from you.

 

[tripptofer]

Trevor-

I appreciate your response.

I too am uncertain if a pivot table is the proper way of addressing my needs, and would have in the past devised umpteen formulas to calculate what it is that I sought. I was assuming that there was a better way using pivot tables, however their functionality, at least as built in, appears to be limited ie, it can to means, but not medians.

I've scoured the net for examples of calculated fields/items, however none that I've stumbled across are appropriate- all seem to be simple arithmetic, or other facile calculations, mostly geared towards finance, and was hoping that I could find a more appropriate template that could better get me moving in the direction I wish to go.

I've attached another workbook that will hopefully illustrate what I am trying to achieve better than my initial attempt. On the first tab you have a ptable of subjects broken down by a particular mutation and gender with the mean age at diagnosis with standard deviation.

Means aren't appropriate for my needs and I am trying to figure out how to get excel to spit out medians. I would also like to be able to compare if there's an imbalance in age by gender using a chitest for each against the cohort as a whole.

It is my understanding that these two examples would represent both a calculated field (chitest?) and a calculated item (median?)- although I might have them backwards.

I'm fairly certain that this is within excel's capabilities, but that there also might be an even better way at arriving at a solution.

I hope this is more clear and do let me know if it is not.

I'm grateful for the help and looking forward to learning more about ptables as I feel that this is a very powerful function that I can certainly benefit from mastering.

Cheers,
tripp

 

[Trevor G]

Tripp looking at the data a little closer (not the actual content so to speak of), does this come to you from a database system as I think an Access Database would be much more suitable, as you can quickly create hard queries that will give you a variety of reports that are at your finger tips.

Let me know your thoughts.

 

[tripptofer]

Trevor-

Well spotted! It was indeed and I am trying to move in that direction for another project.

I know a bit of excel (enough for my purposes, but obviously trying to get better) and very little of access (originally a mac lad and thus only recently shifted to pc b/c of this).

On the access end of things, this data comes from something already and relatively recently made- the data gets dragged out and then crunched by me in excel. I do not have real time access to the, um, access resource.

On another project, am trying to take a bunch of data (attached), shift it into access and make a referential resource out of what I would normally grind through in excel alone. If I am to make any progress on larger and more sophisticated datasets, I know this must change!

So I'm coming at your solution from both ends, however I've been struggling with relationships and referential integrity on the one end and not having a database up and running so that I can play with it and see what can be done on the other (queries, reports, etc...).

I've attached a sheet with an abridged dataset as well as a jpeg of what I'm trying to do in access, relationship-wise (NB, field names in jpg are slightly different than sheet as they were changed after importing into access to fit conventions ie, prefixes).

I've taken a many long stabs at trying to figure this out on my own using books and other examples off the net, but I think I might be missing something fundamental because it doesn't quite behave as expected and I'm thinking I can't do this in vacuum.

Any assistance would be very much appreciated...

tripp

PS, sorry to drag in another issue, but I guess when it comes to learning- it's really all the same in the end...

 

[Trevor G]

Morning Tripp,

OK so it seems as though you will go down the Access route eventually.

What I would introduce you to, to start with is when data is already in place and you try to build relationships, sometimes it wont work especially if trying to marry fields that aren't the same data type or have conflicts.

A main table like the data you attached would be a good start point, as you can use this then build smaller parts on stage by stage.

So if you take this data then you can build a variety of queries to give you stastistical information on it.

I will be travelling home later on a train from London for a few hours so I will look to do a little on a new database then upload it so you can see if you can take it further.

Obviously I am aware that this is only a small amount of data from the whole database but it will give an introduction.

Having good Excel skills will be useful, jsut a few tweaks will help.

Trevor

 

[tripptofer]

Much obliged trevor- very much looking forward to learning and finally getting some traction on this as I know that a whole new world will open up.

Thanks again,
tripp
eltryptophan@gmail.com