Sum up statistical results from 12 worksheets

I have 12 worksheets of certain data, that is one worksheet for every month 
of the year (i.e. Jan-Dec). In each worksheet/month I have already calculated 
the 
following functions/formulae:
SUM
MIN
MAX
AVERAGE 
STDEV
Data above the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
Data below the AVERAGE (e.g. 
=ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))

So, I now have this 13th spreadsheet that lists the results from the 12 
worksheets/months. For example:
Row C1:N1 Jan Feb Mar .... Dec lists the individual results from SUM
Row C2:N2 Jan Feb Mar .... Dec lists the individual results from MIN
Row C3:N3 Jan Feb Mar .... Dec lists the individual results from MAX
Row C4:N4 Jan Feb Mar .... Dec lists the individual results from AVERAGE 
Row C5:N5 Jan Feb Mar .... Dec lists the individual results from STDEV
Row C6:N6 Jan Feb Mar .... Dec lists the individual results from Data above 
the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
Row C7:N7 Jan Feb Mar .... Dec lists the individual results from Data below 
the AVERAGE (e.g. 
=ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))

My question is how I can summarize in Column M1:M7 the results I got 
for each formulae/month. For example, SUM is pretty 
straightforward as you only need to =SUM(C1:N1). But, my problem is how to 
accurately sumarize the other results.

Thank you for your help.

Orlando

0
7/7/2006 2:10:02 AM
excel 39879 articles. 2 followers. Follow

1 Replies
397 Views

Similar Articles

[PageSpeed] 5

ORlando,

SUM  use SUM
MIN  use MIN
MAX use MAX

Average - use SUM (From above) / COUNT of all 12

Stdev, and some other populations statistics, require that the numbers be passed in their entirety 
to the function.

Ideally, you should change the structure of your workbook and use ONE sheet of data, with a column 
of identifiers (could be date, or month name), and then use a pivot table to get your data 
statistics.  Much easier to maintain, and certainly faster and  less error prone.

HTH,
Bernie
MS Excel MVP


"OrlandoFreeman" <OrlandoFreeman@discussions.microsoft.com> wrote in message 
news:0322356C-52AF-406A-88CF-1D9AD1581821@microsoft.com...
>I have 12 worksheets of certain data, that is one worksheet for every month
> of the year (i.e. Jan-Dec). In each worksheet/month I have already calculated
> the
> following functions/formulae:
> SUM
> MIN
> MAX
> AVERAGE
> STDEV
> Data above the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
> Data below the AVERAGE (e.g.
> =ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))
>
> So, I now have this 13th spreadsheet that lists the results from the 12
> worksheets/months. For example:
> Row C1:N1 Jan Feb Mar .... Dec lists the individual results from SUM
> Row C2:N2 Jan Feb Mar .... Dec lists the individual results from MIN
> Row C3:N3 Jan Feb Mar .... Dec lists the individual results from MAX
> Row C4:N4 Jan Feb Mar .... Dec lists the individual results from AVERAGE
> Row C5:N5 Jan Feb Mar .... Dec lists the individual results from STDEV
> Row C6:N6 Jan Feb Mar .... Dec lists the individual results from Data above
> the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
> Row C7:N7 Jan Feb Mar .... Dec lists the individual results from Data below
> the AVERAGE (e.g.
> =ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))
>
> My question is how I can summarize in Column M1:M7 the results I got
> for each formulae/month. For example, SUM is pretty
> straightforward as you only need to =SUM(C1:N1). But, my problem is how to
> accurately sumarize the other results.
>
> Thank you for your help.
>
> Orlando
> 


0
Bernie
7/7/2006 11:50:24 AM
Reply:

Similar Artilces:

results table dilemma
hi there everyone, I'm hopeful that someone can help me with my excel problem. Here's my dilemma: I'm trying to do a results table where results will come from a worksheet that has different columns, one of which is a date column(let's call it worksheet A). Now the results table is in another worksheet in the same workbook, and before results are generated, I'd like the user to specify a date and then the results table will be populated with entries from worksheet a that matches the specified date and dates from the previous week. im not quite sure if it's possi...

Date Format 12-10-03
Hello all... trying to create a phone call activity and set the 'ActualStart' datetime value, but CRM is complaining that it's not in a correct format. I'm passing it in standard .NET format, "MM/dd/yyyy hh:mm:ss am/pm". What format is it expecting?? Thanks! -mdb assume you have a string containing your date.. called start - format crm needs is shown below ======================== DateTime MyDate = System.Convert.ToDateTime(start); String mydate = MyDate.ToString("yyyy-MM-dd HH:mm:ss"); strXml += "<scheduledstart><![CDATA[&qu...

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Date-range specific report with counted results of combo box selections
Hi there, I'm a novice with Access, but am currently setting up a patient database for an NGO in India that I'm volunteering with. The primary reason for the database is to collect statistics quarterly. It is a simple database with one table that collects patient data from simple forms with some fields having combo boxes. Ultimately I want to generate reports that will show a summary of 'counts' of various fields for a given period. For example: In the table I have a combo box for where the person came from, with several options including - "Pick up Y. Bazar", &quo...

Different password for different worksheet " Help !"
Dear Sir, Greetings from Qatar ! I am using MS Excel 2007. I want to create a workbook with different worksheets for different users to be able to view and edit the content on his/her designated worksheet. i.e. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can view & edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to view and protect. Is it possible? Please kindly ...

Junk Mail #12
I am using Outlook 2003 and periodically I get the notice that Outlook has downloaded a message that appears to be Junk E-mail and that it was automatically moved to the Junk E-mail folder but when I open the Junk E-mail folder, nothing is there. Why am I getting this message? I am also using Qurb and suspect that messages being quarantined are the same ones that would have gone into the Junk E-mail folder. it's probably a timing issue larry. Outlook applies it's rules and filters and then Qurb respectfully waits till she's finished. So, as Outlook informs you of her in...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

Money 2004 #12
On the account list in money it will not show subtotals and on the final total is has # # err # #. After each account it has the final tolal on each account and they are all the same. Pleas some one help. Thanks Bob ...

Creating a Worksheet with date
Hi, I'm trying to creat a wroksheet that will have two weeks of date at the top row to be printed every two weeks and each time printing, it shows the next two weeks automatically. I'm very new on doing this, please help if there is anyway of doing this. Thank you in advance for your help. Regards, MD Thanks Paul, It worked. MD >-----Original Message----- >Md, how about putting the first date in A1 and then this formula in B1 and >copy accross to as many columns as needed, then you will only have to change >the date in A1, this could be changed automatically wh...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Unpivoting a worksheet
Hello, I have tried to search for tips on how to unpivot a worksheet, and it seems that everyone is refering to the article http://j-walk.com/ss/excel/usertips/tip068.htm .. This article, however, does not apply to Excel 2007 which I use. Could anyone please tell me how to do this in Excel 2007? Thx, in advance Baard Highlight all the cells in the Pivot Table. Hit the DEL key. Save the file. Job done! If my comments have helped please hit Yes. Thanks. "bvdahl" wrote: > Hello, > > I have tried to search for tips on how to unpivot...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Worksheets within a worksheet?
I need to make a separate worksheet for each State, which I can do, but within each one of the State worksheets I need a separate worksheet for each month. So I can tab through the information for each month for each State. Essentially, when I click on a State, a new set of tabs labeled with the months starting with January appear and then I am able to scroll through each month's information for that particular State. Is this possible? No "ceemo33" <ceemo33@discussions.microsoft.com> wrote in message news:C16642A8-7471-4AF1-9232-79D18F627A26@microsoft.com... : I need t...

Drop down values relating to cells in next worksheet
Here goes, hope this makes sense! I have a worksheet that contains a questionnaire, which is filled in by choosing the reply from the drop down (Data/Validation) box, e.g. 'Strongly Agree', 'Disagree' etc. I need to be able to make these choices relate to my scoresheet in the next worksheet, so that when 'Strongly disagree' is chosen, a score of 10 is transferred to the related cell in the scoresheet. There are 4 choices - strongly disagree = 10, disagree = 9, agree = 1, strongly agree = 0. I haven't a clue how to do this, hope someone can help! Hi use v...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

How to use outline data (grouped rows) in a protected worksheet?
I want to protect a sheet containing outline data in grouped rows. After protection, users cannot hide or display the rows using the expansion buttons in the margin. Is using outline data impossible in protected sheets? ...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Sum sum sum
Let me describe my problem... I have a form with with item number with is unique and then i put another form (subform) to linked to record the number of item in and out.... This means a item can have many in and out transactions which make the item number not unique.. My problem is I want to calculate the sum of in quantity within the same item. I want the sum to be keep on increasing when the user enter the quantity. Example user key in 2 then new user key 4 and i want the total quantity to be 6... Please help.... No idea of what i am talking? I will explain... -- Message posted via http:/...

sending emails from worksheet
I have Excel 2000 and have a worksheet with about 300 email addresses on it. I need to send an email to all those addresses. How do I do it without cutting and pasting into Outlook Express? Ta Nikwak ...

Taking sum of a field from sub report
Hi On a sub report I have a field datetot which is the sum of two fields those two fields are in turn coming from two further sub reports of the sub report. How can I take the sum of datetot field to the main report? Thanks Regards See: Bring the total from a subreport onto a main report at: http://allenbrowne.com/casu-18.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John" <John@nospam.infovis.co.uk> wrote in message news:OCWbU...

Summary worksheet within a workbook
I'm sure this is an easy formula, but I need some help.... I have a workbook where a tab is added with each day's activity. I'm looking to add a summary page that will pick up the data from all tabs starting on line 6 of each tab (including additional tabs that may be added). Any suggestions? Thanks You'll want to look into 3D functions, assuming you're wanting to gather sums/counts of data. -- Best Regards, Luke M "nitengale" <nitengale@discussions.microsoft.com> wrote in message news:880943E9-0425-49FF-86F1-7CBF00A82BC0@microso...