# Calculating an average using VB

#### chammy88

##### Board Regular
Hi all,

I have two columns, one with dates and one with densities. In the dates column, there could be many of the same date with different density values. The dates are in chronological order.
I started with something like if Date(i) = Date(i)+1 then
.......
but everything I had after that wasn't correct.
It needs to say if the dates are the same then sum the densities, count the number of densities to be summed and divide the sum by the number for the average
My spreadsheet is set out like the example below.
eg. Date Density
06/06/2012 2
06/06/2012 2.5
05/06/2012 1.9
04/06/2012 5

Thanks

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I am the first one to want to use VBA as a solution, but why do that when Excel's built in Pivot Tables would do the trick?

Do you have to have a macro?

If the range is A2:B5 and since the dates are in descending order you can use this formula...

=IF(A2=A3,"",SUMPRODUCT(--(\$A\$2:\$A\$5=A2),--\$B\$2:\$B\$5)/SUMPRODUCT(--(\$A\$2:\$A\$5=A2)))

...from Row 2 in any free column and to Row 5 of that column.

HTH

Robert

Replies
3
Views
119
Replies
0
Views
64
Replies
15
Views
350
Replies
3
Views
88
Replies
5
Views
172

1,203,630
Messages
6,056,422
Members
444,863
Latest member
powerdt

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