Averaging Values In Column B For Recurring Dates In Column A

Mar 10, 2014

I have a set of data with daily total returns (TR) for 218 companies on the FTSE350 between 2007-2013. The Data looks as follows:

Company ..............Date(daymonthyear)..... TR
1........................... 112007 ..........................10.4
1 .......................... 212007 ..........................10.2
1 .
1 . .
1 .......................... 30102013 .........................12.3
. . . .
. . .
. . .
2............................112007 .......................... 4.5
2 ............................212007 ..........................4.6
2 .
. .
. .
Etc.
. .
. .
218.......................30102013 .....................13.2

I want to perform a dickey fuller test to see if there is a unit root, but for this I can’t have repeated time values, thus I need to create a daily average TR for each day (Thus summing up all TR values for all companies that have data on TR available on that day and averaging them).

How in excel can create an average value for TR every time a series of numbers in the “date” column take the same value? The best thing possible would then be if this could appear so that I have a data set that looks like

Date (daymonthyear)........ TR
112007 ..............................13.2
.
.
.
.
30102013............................ 9.4

View 2 Replies


ADVERTISEMENT

Excel 2013 :: Averaging Values Of Rows From Particular Column

Aug 6, 2014

Is it possible to perform average function on subsequent group of rows and make a new set of rows ?

For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....

Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.

Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12

View 4 Replies View Related

Sum Values In Column That Correspond To Column Of Dates Between Two Specific Dates?

Jun 25, 2012

What I am trying to do is sum values for each day of the month up to a designated date.

Example:

A1=any date of the month

A2-A31 = 6/1/2012-6/30/2012
B2-B31 = values that correspond to each date

how can i sum the values in column B from the beginning of the month to A1?

View 3 Replies View Related

Convert Column Of Dates Into Column Of Date Values?

Dec 9, 2013

In a worksheet, how do I convert a column of dates into a column of each date's numeric value?

View 7 Replies View Related

Excel 2007 :: How To Associate Recurring Name In One Column

Dec 3, 2011

Using Excel 2007, how can I associate a recurring name in one column with one and the same number in another?

Ex.: "Jones" in C8 = "1" in G8
"Smith" in C9 = "1.5" in G9
"Williams" in C10 = "2.5" in G10

I hope my question is clear enough.

View 2 Replies View Related

Count Number Of Cells Between Recurring Cell In A Column

Aug 15, 2012

code that will count the number of cells under a "title cell" that is recurring in a column, and then divide the result by 2. The result will then be displayed in another column preferably aligned to the "title cell" (in this case "Items") in column A.

For example:

Before code is applied

A1: Items
A2: Items
A3: four-legged
A4: dog
A5: two-legged
A6: chicken
A7: Items
A8: four-legged
A9: cat

[code]....

After code is applied to column A

A1: Items B1: 0
A2: Items B2: 2
A3: four-legged
A4: dog
A5: two-legged
A6: chicken
A7: Items B7: 1
A8: four-legged
A9: cat

[code]....

View 9 Replies View Related

Averaging Column If Something Happening In Another Column

Aug 17, 2012

I am trying to average the ages of men and women separately. Here is what I need. Column B (B4:B99) indicates whether the participant is male or female, and Column C (C4:C99) has their respective ages. I would like to calculate the average age of the men and women. Number of 1 in Column B denotes a male, and a number of 2 denotes a female.

View 2 Replies View Related

Excel 2007 :: Move Recurring Data From 1 Column To Multiple Columns

Jun 6, 2014

I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:

AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..

I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...

What would be the best way to accomplish this?

View 4 Replies View Related

Sum Values In Column Based On Dates In Another

Mar 13, 2008

I'm try to lookup a range of dates in a table, and sum up the data values that is equal or less then the specified dates.

Please view the attached spreadsheet for example.

View 4 Replies View Related

Adding And Averaging Every Other Column In One Row?

Jul 27, 2013

I've been searching for the answer but either I do not understand or it isn't out there. I want to add every other column in one row resulting in an average of all the columns in that one particular row.

View 8 Replies View Related

Automatically Averaging Consecutive Data In A Column

Jan 10, 2014

How can I automatically average consecutive data (4 rows) in a column and jump to the next 4 rows...until the end of my column?

View 4 Replies View Related

Dynamic Averaging Based On Adjacent Column?

Nov 2, 2013

I have a column with random values that are generated my my machinery.I should not sort them, because they provide me with the performance graph. Lets say i have column "A"

0
2
9
9.5
30
90
22
15
9
0

As the max value of this is 90, and its 10% of MAX is 9, which occurs at 3rd and 9 th positions.I need to find the average of its adjacent range, with zeros not considered.It has to calculate the max value in the column, find 10% of max and do average of adjacent column from the sl. no of first occurrence of 10% value in first column to the sl no of last occurrence of 10% value in first column.

the exact 10% value may not always exist. 10% of 90 is 9, but if the max value is 94, the 10% would be 9.4 which doesn't exist. then it has to take the nearest available predecessor in that case.i get values in decimals mostly and only some of them are perfect integers, thus this consideration goes with it,and also, my range actually is B14:B10013

i would most probably have the 10% value within the first 50 "non zero" entries and reoccurring somewhere probably within the last 50 "non zero" entries.

View 7 Replies View Related

Excel 2010 :: For Selected Date Between Dates Sum Values In Another Column

Jan 26, 2013

We have arrival dates and departure dates for guests and would like to figure out the number of meals needed on a particular date.

Named ranges: NumGuests _ArrivalDate _DepartureDate _CheckInTime _CheckOutTime
Need number of breakfasts
Number of Lunches
Number of Dinners for a given day of the week.

So if a guest is here say Jan 25 arriving after 9am, staying 2 nights they will have Lunch and Dinner on the 25th, BLD on the 26th and BL on the 27th. Since by default most arrive after 9 (miss breakfast), we can eliminate the checkin time for the breakfast calc and make the first one for the next day.

There might be other guests staying through that time period. The ******* would like to know how many meals they will need to prepare for the week.

Windows XP, Excel 2010

View 5 Replies View Related

Summing/averaging A Column Of Varying Number Of Rows

May 18, 2007

how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?

I have tried relational positioning e.g

each time a row is created, integer n5 = n5 + 1

Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n2 = ActiveCell.Offset(rowoffset:=1, columnOffset:=-19).Address
n5 = 0

Where n5 is augmented each time a row is created then set to 0 to count the next list

View 9 Replies View Related

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 Replies View Related

Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month

May 8, 2009

how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

View 3 Replies View Related

Date Column (insert In The First Column Dates Starting From 01/01/2000 All The Way Up To Today)

Nov 11, 2008

I'm trying to create an excel worksheet as follows:

Column1 Column2 Column3 Column4 Column5
Date Time In Time Out Hours Worked Pay
01/03/2000
01/04/2000
01/05/2000
.
.
11/11/2008

I want to insert in the first column (Column1) dates starting from 01/01/2000 all the way up to today, 11/11/2008. So I would end up with a unique date on each row.
To accomplish this manually would be a daunting task. I'm a newbie to excel and don't know how to use any in-built functions.

View 3 Replies View Related

Find Median Of Set Of Numbers On Column That Correspond To Dates On Left Column

Sep 9, 2013

I am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:

=IF(COUNTIFS(A:A,">=10/1/12",A:A,"

View 4 Replies View Related

Count Values Of Column If Column Header Is Equal To Name In Column A?

Apr 25, 2014

For column "B" count all the 1's if column header is equal to name in column A.

For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.

View 5 Replies View Related

Formula That Counts Unique Dates In Column Based On Another Column Value?

Jan 8, 2014

How would I count unique dates associated with another specific value on a worksheet?

For instance, Lets say I want to count each day a particular person makes a sale.

Example:

Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014

So on another worksheet, I'd have:

Sales Person Days with Sale
Don 3
Jack 1
Mike 2

The Formula would go into the "Days with Sale" Column.

View 2 Replies View Related

Search A Column Of Dates & Return Data From Another Column

Jun 8, 2009

I am trying to get a formula that will search one column range B and pull data from another, D. Dates are down column B and some of those dates are repeated several times. In column D there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column A, there will only be data entered in 1 of the cells of column D and blank cells in the other 2.

Column B -- Column D
07/06/09 -- 54000
08/06/09
08/06/09 -- 62000
08/06/09
09/06/09 -- 61000

I couldnt get the LOOKUP function to work properly, as there are duplicate dates in column B and I often got a result of 0 returned.

As Ive only got 1 piece of data added in column D per day and any duplicate days would just have blank cells in D, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesnt feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.

View 6 Replies View Related

Highlight Dates In Column H If They Are Greater Than Column G By Certain Number

Sep 5, 2013

I need formatting to highlight the dates in Column H if they are a greater than a week or more from Column G. Tried some different ways of doing this with the conditional formatting but cannot get it to work yet

View 12 Replies View Related

Get Number Of Records / Samples In Column C Between Two Dates In Column A?

Mar 6, 2014

I am trying to get the number of records/samples in column C between two dates in column A.

View 9 Replies View Related

Check All Dates In Column B Then Add All Corresponding Cell In Column C

Jun 22, 2014

I am trying to check column b which hold dates then add up column c if the month is the same

B C
02-may-14 0.05
02-jul-14 1
11-Jun-14 14
20-Jun-14 0.34
26-Jun-14 15
26-Jul-14 21

This what come up with

=SUMIF(B2:B140,"02-jun-14",C2:C140)

But this will only add up dates that are 02-jun-13 I would like it to add up all dates in June.

View 2 Replies View Related

Sumproduct -in Column A I Have Dates And In Column B I Have Names

Jan 30, 2008

in column a I have dates and in column b I have names.

eg

A1 = 1/1/08
A2 = 2/3/08
A3 = 3/1/08
A4 = 3/1/08

B1 = Jenny
B2 = Jenny
B3 = Jenny
B4 = Pat

I am trying to count the number of instances of "Jenny" in January.

I tried =sumproduct(A:A,>=39448

View 9 Replies View Related

Counting Recurring Values From Two Lists

Jul 3, 2013

I'm looking to find a solution to count the totals for values which are linked to a specific code. For example:

A b
ex1 22
ex4 18
ex3 10
ex4 3
ex1 23
ex2 11

So I want a formula that will count the totals for each group in column A

The answer would be
ex1 45
ex2 11
ex3 10
ex4 21

View 2 Replies View Related

VBA - Generate Random Column Values From Existing Column Values?

Aug 22, 2013

[URL]

I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.

View 2 Replies View Related

Extract Largest Values From Column B And Corresponding Text Values In Column A?

Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100

The desired outcome

Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

View 6 Replies View Related

Calculate Mean Of Column B Values Based On Date Values In Column A?

Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4

View 2 Replies View Related

Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?

Dec 24, 2013

I have two columns in excel, col A and col B.What I would want to achieve is following:

Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3

Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).

[URL]

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved