Sum Different Rows & Columns

I need some assistance with making the following calculation.   Eac
week a person shoots a score. They can have anywhere from 1-12 score
over a 12 week period, but no more than 1 per week.

Each week a calculation is made to generate "bonus points" which ar
added the next time they register a score.

I need to find a way to:

1) Locate the current week's score for each unique person.

2) Add the "bonus points" calculated in their previous record (which i
tabulated in a different column).

The data is in the following format:

Date Person Score Total Bonus
15-Jan  Mike 10  10  5
15-Jan  Jane 15  15  3
22-Jan  Mike 17  22  2
22-Jan  Jane 10  13  4
27-Jan  Jane 15  19  3
27-Jan  Mike  5    7   8

Thanks in advance for your assistance

--
Message posted from http://www.ExcelForum.com

0
1/28/2004 11:49:44 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
687 Views

Similar Articles

[PageSpeed] 32

Dear Tally

My suggestion would be to create a pivot table based on 
the sheet containing the scores (The sheet containing the 
sample data you provided).

Firstly I would insert two new fields into your 
data, "Week Number" and "Period Number"
In the "Week Number" field enter =WEEKNUM(C2,2) where C2 
is the first cell containg the date.
In the "Period Number" field enter =IF(B2 <12,"Set 1",IF
(B2 <12>=24,"Set 2",IF(B2 <24>=36,"Set 3","Set 4"))) where 
B2 is the first cell containing the week number calculated 
above. You can obviously change the week number parameters 
in the formula above to suit the range that you want. Copy 
and paste bot of these formulas into as many cells down as 
you have dates for.

The reason for adding these two new fields is that they 
will be handy for using in the pivot table.

Inset a pivot table on a new worksheet. Use $A$:$G$ as the 
range.this will show all the data that you want, 
summarised by person and week number. In my test I used 
the week number as a column field, the Person as the row 
field, and both the score and bonus in the data area. I 
put the Period number in the page field area, but it could 
just as easily be placed as a row field.

I hope this goes some way to solving your problem

Paul

If you need more detail (eg a step by step based on the 
above) please feel free to email me direct.

Kind regards

Paul
>-----Original Message-----
>I need some assistance with making the following 
calculation.   Each
>week a person shoots a score. They can have anywhere from 
1-12 scores
>over a 12 week period, but no more than 1 per week.
>
>Each week a calculation is made to generate "bonus 
points" which are
>added the next time they register a score.
>
>I need to find a way to:
>
>1) Locate the current week's score for each unique person.
>
>2) Add the "bonus points" calculated in their previous 
record (which is
>tabulated in a different column).
>
>The data is in the following format:
>
>Date Person Score Total Bonus
>15-Jan  Mike 10  10  5
>15-Jan  Jane 15  15  3
>22-Jan  Mike 17  22  2
>22-Jan  Jane 10  13  4
>27-Jan  Jane 15  19  3
>27-Jan  Mike  5    7   8
>
>Thanks in advance for your assistance.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
paul_falla (99)
1/29/2004 12:24:03 PM
Reply:

Similar Artilces:

Login to CRM as Different Domain User
Good day everyone, I've just installed the Trial of CRM 4.0 so that we can take a look at it. It's working well, and we have several people entering data into it - so far, so good. I have one question though. We have many computers on the production floor that are 'floaters'. In other words, they stay logged on as a default user who has few permissions, and allow our employees to access OWA and a few other Intranet services when they are on the floor and away from their desks. Is there a way to launch the CRM Web client with different user credentials than those tha...

Use on load trigger to provide Views for different Roles
I would like to provide different user roles with different views for the Account entity. Therefore I would like to use the on load trigger to check which user is logged on (and his user role) and according to this show a defined set of fields (maybe provide standard values as well). Is it possible to hide complete folders? Thx Ronald Lemmen's blog has code to do this: http://ronaldlemmen.blogspot.com/2006_05_01_archive.html -- Tad Thompson "Sascha Reppel" wrote: > I would like to provide different user roles with different views for the > Account entity. Theref...

deleting multiple rows
Hello: I need to be able to delete multiple rows out of a worksheet. What I need to be able to do is have a function/macro that can identify a string of characters and then delete all the rows that have those characters. For example, if row 4, 18, and 45 all had "blue" in them (assume column is A), then I need something to go delete row, 4, 18, and 45. At the top of my worksheet I will have several, probably about 3, criteria, such as, "blue", "red", and "green" that will all need to be deleted. Now here's where I really need help. A row may ...

Decreasing Row Height MS Project 2003
Hi, This is very very lame, but I think I have done something to "block" row heights in MS Project. I want to decrease them, but I simply can't! I can increase them though... Please help!! Thanks Hi, Through Bar or Bar Styles, did you add text above or below a bar? That wil force teh row height to no less that 2. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Diogo" <Diogo@discussions.microsoft.com> wrote in message n...

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the first 5 columns that limits the range of the sort. How do I remove it? Using Office 2003. Maybe if you remove the Data|list Select a cell in that blue box. Data|list|convert to range jrw562 wrote: > > How do I get the whole spread sheet to sort? There is a blue lox for the > first 5 columns that limits the range of the sort. How do I remove it? > Using Office 2003. -- Dave Peterson ...

Hide columns according to background fill color
I am having trouble understanding how Excel handles colors. I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType. In another module, I use the TermColor variable as follows: Sub WeedColsByColor(ByRef Clr, ByRef WS) Dim LastCol, i As Long With Worksheets(WS) LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'hide columns if they have one of the forbidden colors Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr)) ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths. Is this possible? -The 1st sub-report has 1 column that occupies the entire width of the page -The 2nd sub-report can fit 2 columns in the page width -The 3rd sub-report can fit 3 columns in the page width Subreports can have any number of columns that don't have to be the same from one to another. Typically your columns should display across then down in order to render properly as a subreport. -- Duane Hookom Microsoft Access MVP "SheldonHinds" wrote: > I want to create a r...

ActiveX looking differently in test container and Excel
Hi, I developed a test ActiveX. When I run it into VC6 test container tool, it's all right: its content is painted into the bounding box of the ActiveX. Instead, when I run it into Excel, its content is painted into a new window. This is a 3D rendering window, created by Coin3D library. It's managed by a class called SoWinExaminerViewer. The class ctor wants a HWND as a "parent window" (e.g. a static control in a dialog box, etc.). As parent window, I use in my code the m_hWnd member of the ActiveX. It is OK when the ActiveX is run into VC6 test container, but a new wind...

Report Sum
I am working on a cost study form for a body shop. Each vehicle will have its own page. I have to be able to add up to 15 different list part totals as well as 15 different net part totals. I then need to add the 7% tax of the list part total to the net parts total. I would love some input on what would be the easiest way to achieve this because I then have to generate a report with by the different companies showing total parts, total labor and total profit ratio. Usually, you want to text put boxes in footer of the form. You have to pull the footer down on the bottom to have...

Sum Days into Weeks
I would like to save myself a whole lot of work please... I have two spreadsheets, one has all the days of the year across the columns, and the next has all the week ending dates across the colums. What I need to do is sum the data in each of the rows below the daily dates into weekly chunks on the same rows in the Weekly spreadsheet. I have also added these up into montly chunks, but there was only 12 sums to do so wasn't too bad. I don't relish having to do 52 of them. Any assistance would be appreciated. If you have *all* the dates for a year across a row then you mus...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Combine rows to print on one label
I have imported student information into an excel spreadsheet (use Excel 2003). Each student has one row of information for each course they take. I need to print one label for each student and each course & grade must print on a single label. How do I combine the rows to cause this to happen? assuming the info in the row is divided into separate columns for name, course, grade, etc you can do a mail merge in word using the spreadsheet as the data source "Clearcreek" wrote: > I have imported student information into an excel spreadsheet (use Excel > 2...

Column width
In Sheet 1 I have a certain amount of data, I want to select some cells and copy them to Sheet 2 keeping the same format. When I do this, the fonts and the colours remain unchanged, but the column width don't. I have tried paste special, but couldn't figure it out. Is this possible? Thanks in advance Regards, Emece.- --Copy range --Select the target cell and right click >PasteSpecial>All>OK --Keeping the target selection right click>PasteSpecial>select ColumnWidth>OK If this post helps click Yes --------------- Jacob Skaria "Emece"...

How do i keep the colors applied to the specific rows when sortin.
I have applied specific colors to specifc rows, but when i sort the colored rows follow the sorting format. ...

deleting duplicate rows
I have an import file that might have duplicates that I don't want to deal with. I'm creating a temp table from the import file and want to delete the duplicates from the temp table before proceding with other code. Here's an example table: create table #temp (itemnum int, firstname varchar(10), notes varchar(50)) insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some note') insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some other note') insert into #temp (itemnum, firstname, notes) values (2, ...

conditional running sum
Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields: Date Adbn% 01/01/09 (Mon) 5% 01/02/09 (Tues) 6% 01/03/09 (Wed) 9% 01/04/09 (Thurs) 6% 01/05/09 (Fri) 8% I need it to end up with the following: Date Abdn% 01/01/09 5% 01/02/09 5.5% Avg of Mon&Tues 01/03/09 6.6% Avg Mon...

Add values in a column according to value in another column
How can I add the values in a column according to values in another column? If there is any value in a row in column B, I want to include the value of the corresponding row in column A. I'm flexible as to whether this is ANY value (i.e. not empty) or greater than zero. Hi Paul Maybe something like this =IF(B1="","",IF(B1>0,B1+A1)) Regards Cimjet "Paul Kaye" <paulmjkaye@gmail.com> wrote in message news:05befaf3-9ba8-48c8-aebb-654f0269d1dc@34g2000hsf.googlegroups.com... > How can I add the values in a column according to values in another > colu...

Moving Exchange DB's to a new server (different name)
In a DR test, we moved SG's to a new server, different server name, same SG and MS name. MS mounts, can not connect to mailbox. Delete mailbox, create a new one, we can then connect. Delete this mailbox, reconnect to the original, and now we can connect. How can we accomplish this in bulk? In other words, after doing a system state restore of AD, then a system state of Exchange, restored MS file, mounted, etc. We were unable to connect to these mailboxes. I am assuming this is related to GUID's. Any thoughts? Users in AD specify a server by name. When you say that you move mailbo...

How do I make a column be my default column in Access
I need to make my desricption field my default field. How do I do that? Right not it defaults to my items field. me.controlname.setfocus or in macro GoToControl "controlname" Bonnie http://www.dataplus-svc.com michelle wrote: >I need to make my desricption field my default field. How do I do that? Right >not it defaults to my items field. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 Or, if you don't want to use events, simply set your tab order from the form design view. -- Frank H R...

Resize rows in a protected sheet
Hi there. I'm sending out a protected worksheet with some cells that the users can enter variable amounts of text. I've set the size so they can comfortably fit in about 50 words but I'd like them to have the flexibility to resize the row height when they're editing the worksheet. But because it's protected, they can't. Is there anyway I can allow them to change the row height dynamically? A button or something?? Andrew A workaround. Format the alignment in the cell to Wrap text. Users can Keep entering text or returns (ALT+ENTER) in the cell and it will aut...

Copying non-adjacent columns to adjacent rows
Hi all, I consider myself fluent in Excel, but I've developed a situation that has stumped me. Any help would be much appreciated. I might be able to solve this issue if somebody could show me how to add a number to a column. For example, if I want Excel to pull data from Column D, how can I get Excel to realize that column D is really the same thing as Column A + 3? I know you can use the column() command to get the numerical value for a column, but is there a way to have it do that in reverse, such that you could tell it the column number is 4 and it would know that you are referring...

Autofit rows
I am having an issue with autofitting rows in a spreadsheet of imported text in Excel 2003. There are no merged cells in the document, and none of the cells go over the 1024 character limit. When I select all rows and attempt to autofit the rows to the contents of the cells, some of the rows resize properly, and some leave one or several lines of white space below the bottom of the text in the longest cell. I can resize them manually, but this is data that is imported daily and runs to several thousand rows. If I widen the column where I am having the problem, once it reaches a certain ...