Minding a match and finding the average.

melfinance

New Member
Joined
Jan 20, 2005
Messages
7
I have a spreadsheet the contains the prices of 20,000 securities at the end of month in two different months: say November and December.
Each security is listed by its cusip (identifier), the account that its in and the end of month price for the total account holdings. So for two months there would be three columns of data for each month for a total of 6 columns.
What I have to do is find the average price between the two months.
I usually sort by account and then when both accounts have the same security I just add them and divide by two. The problem is that every month some accounts no longer exist or no longer hold the security. Also some accounts get added and some securities get added. I need a way for excel to look and see if there is a match between the security and the account it is to sum the prices and divide by 2. It the security is new it needs to add a 0 and divide by two. So for every scenario where there is something in one month but not in the other it should simply add by 0 and divide by two. Sounds easier than it is. :(
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
If you have the price columns for the two months for eg. C and F, if you use AVERAGE function as follows:

=AVERAGE(C2,F2)

every time one column is blank the average will be equal to the only price displayed in the other column, which is not responding to your need to calculate. To force considering the blank cell as 0 use N function, i.e.:

=AVERAGE(N(C2),N(F2))
 

melfinance

New Member
Joined
Jan 20, 2005
Messages
7
That would work except I don’t have any blank columns. If a security is no longer held it is simply no longer on the list. So what I have do is go into this months list and add in the previous months information for the security and account and enter zero in the price column. It is the same if a security is added in one account that didn’t hold it last month. I need to go back and add in the account and the security and set the price as zero. This takes forever with so many cells.
 

Forum statistics

Threads
1,147,846
Messages
5,743,518
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top