SUMIF Date / Average Help

[bomberchia]

Hi,

I have faced with a problem with Excel when I wanted to find the average for a stock price.

I have 2 columns:
1st Column: Dates from 1st May 2007 till 23th December 2008 (Rows:A3:A403)
2nd Column: Stock prices from 1st May 2007 till 23th December 2008 (Rows:B3:B403)

So far, what I have researched is coming to the conclusion of using:

SUMIF(range,criteria,sum_range)

May I ask how would I modify this code so that I can input it into a macro whereby the SUMIF function can automatically sort if A3:A403 belongs to which months and then calculate the average of the stock price?

[Kieran]

bomberchia,

it may be easier to use the pivot table functionality available in excel.

Assuming your data has headings at the top of the columns, set the active cell to any part inside the list.
Go to Data>PivotTable and PivotChart Report.
When the wizard box pops up , just press finish ( it normally does everything you need)

You will not be pesented with a new sheet and a Pivot table field list dialog.

Grab the date field from thePivot table field list dialog and drop it over the left of the sheet. (Where it says 'drop row fields here')

Drag the stock price from this dialog to the large box at the top left of the sheet (where it says 'drop data items here')
At the top of the box will now be a button that says 'Sum of...' . Double click the button and change the summarize by to Average and then press OK.

Grab the date filed from thePivot table field list dialog and drop it over the left of the sheet. (Where it says 'drop row fields here')

Now select any of the dispalyed dates, right click and select 'Group and show detail' > group.

Then select Months.

You now have a table of all the month verage stock prices.

[bomberchia]

bomberchia,

it may be easier to use the pivot table functionality available in excel.

Assuming your data has headings at the top of the columns, set the active cell to any part inside the list.
Go to Data>PivotTable and PivotChart Report.
When the wizard box pops up , just press finish ( it normally does everything you need)

You will not be pesented with a new sheet and a Pivot table field list dialog.

Grab the date field from thePivot table field list dialog and drop it over the left of the sheet. (Where it says 'drop row fields here')

Drag the stock price from this dialog to the large box at the top left of the sheet (where it says 'drop data items here')
At the top of the box will now be a button that says 'Sum of...' . Double click the button and change the summarize by to Average and then press OK.

Grab the date filed from thePivot table field list dialog and drop it over the left of the sheet. (Where it says 'drop row fields here')

Now select any of the dispalyed dates, right click and select 'Group and show detail' > group.

Then select Months.

You now have a table of all the month verage stock prices.

Kieran, thanks for your tip!

What you mentioned was a method which I did also explore and it worked successfully. Unfortunately due to the request of clients, they require the solution that of a macro button, hence I'm stuck at a dead end again.

I will upload the data screenshots in a while later to give you a clearer explaination.

Hope to hear from you soon again!

[bomberchia]

Hi Kieran,

I think I know what you are referring to already and I feel that it should be the way to go about it.

just a tiny question, would the pivot table be affected when the data in A3:A403 be affected when it is updated everyday from the trading system? Can your solution still work in that way?

Many thanks once again.

[Kieran]

bomberchia,

The pviottable data will need to be refreshed daily.

This can be done automatically via code, or simply by right clicking in the pivot table and selecting refresh data. It is nearly instantaneous.

[bomberchia]

Hi Kieran,

I have tried following your instructions as given in the earlier post but I still could not make it happen.

I have attached a sample data for reference.

Hope to hear from you again.

[Kieran]

bomberchia,

Is the attached file what you want?

[bomberchia]

Kieran,

Yes it is correct!

Is it possible to use LIVE data from reuters trading to compile into a pivot table just like you have shown me? What I shared as sample was static data.

I did try using the LIVE data and place it into pivot. What it came out was just "########".
?
Any suggestions

[bomberchia]

bomberchia,

Is the attached file what you want?

Yeap Yeap. Thank you so much!

[Kieran]

Does the information here help?