Conditionally Subtracting Dates in One Column and Averaging the Difference

[amerain]

Hello,

This is a simplified version of the data table that I have:

Date Color
February 5, 2013 Brown
February 6, 2013 Orange
February 7, 2013 Red
February 11, 2013 Gray
February 25, 2013 Orange
February 26, 2013 Beige
March 11, 2013 Gray
March 18, 2013 Red
March 25, 2013 Orange

I have created a frequency table based on this information that looks like this:

Color Freq.
Brown 1
Orange 3
Red 2
Gray 2
Beige 1

I would like to add a third column to my frequency table that will subtract the differences between the dates in my data table (from newest to oldest) and average the differences between those values, resulting in a column like this (that I calculated manually for demonstration purposes):

Average Number of Days Between Colors
0
23.5
39
28
0

Is there any type of formula that can do this? I'd prefer to use an array in the spreadsheet itself, but would do some VBA if that is the only way possible, as I don't have much experience with it but could probably muddle my way through...

Thanks so much in advance for help with this!
Amy

[JBeaucaire]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[amerain]

sample_color_workbook.xlsx

Hello,
Thanks for your reply. I think I have attached what you needed - I am looking to calculate the information for the highlighted cells in column J. Please let me know if you need anything else.
Thanks!
Amy