# Minding a match and finding the average.

#### melfinance

##### New Member
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

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Corni

##### Active Member
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
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.

Replies
2
Views
394
Replies
4
Views
345
Replies
1
Views
318
Replies
7
Views
330
Replies
5
Views
640

1,181,169
Messages
5,928,509
Members
436,606
Latest member
ppainter1958

### 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.

### Which adblocker are you using?

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

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