+ Reply to Thread
Results 1 to 3 of 3

Conditionally Subtracting Dates in One Column and Averaging the Difference

  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Question Conditionally Subtracting Dates in One Column and Averaging the Difference

    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

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally Subtracting Dates in One Column and Averaging the Difference

    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

  3. #3
    Registered User
    Join Date
    03-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Conditionally Subtracting Dates in One Column and Averaging the Difference

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1