PDA

View Full Version : Subtract adjacent values based on a condition



abbab
04-03-2013, 12:25 PM
Hello,


I am trying to create an array or other formula (I am open to using VBA event though I am a novice) to select all the dates in a range based on a condition and then subtract the second largest from the largest, the third largest from the second largest, and so on and so forth.


This is a simplified example 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?



Thanks so much in advance for all of your help!
Amy

mdmackillop
04-03-2013, 12:42 PM
I don't follow the colur scenario. For the date differences
=LARGE(Dates,ROW()-2)-LARGE(Dates,ROW()-1)
assuming Dates starts in Row 3, otherwise increase 2 & 1 accordingly.