Calculating an average using VB

[chammy88]

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

 

[lrobbo314]

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?

 

[Trebor76]

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