Moving Data + Filling In Where Data Is Missing (sum if, match, average if, offset)

[katieni8]

Hi all - having difficulty with the following. I've read all the related posts I can find and tried many different formulas, but can't figure this out.

We have Sheet 1 that consists of unique rows of data with columns that are as follows:
ID, Original Price, Date, Price, Date, Price .... Price

These dates are the dates between 10/16 and 12/2 on which a specific object's price changed. The dates also have timestamps because they were originally in epoch time.

Example Data On Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Original Price[/TD]
[TD]Date[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]$40[/TD]
[TD]10/16 4:00am[/TD]
[TD]$38[/TD]
[TD]10/17 3:00pm[/TD]
[TD]$30[/TD]
[TD]10/20 6:00pm[/TD]
[TD]$42[/TD]
[TD]10/20 9:00pm[/TD]
[TD]$40
[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]$50[/TD]
[TD]10/18 5:00 am[/TD]
[TD]$40[/TD]
[TD]10/20 6:00 am[/TD]
[TD]$55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to get this data onto another sheet (Sheet 2), which is organized by the actual date name, with rows of prices for that date below it. I'm going to use that sheet to run analysis on average prices by day, etc. So the column titled 10/16 has all the prices of all the objects on that date, and so on from there. The problem is that individual objects only have data on dates where their price changed (and no data on any other dates), and in some cases, objects have different price values for the same date, because its price was changed more than once in a day. I've outlined correct answers for Object X in the Chart below.

Where objects didn't have a price specifically called out in Sheet 1, I need to show the previous date's price. Then if there are repeats (i.e. two different price values on the same day), I calculate the average. So from the data above, on 10/20 the price changed twice, so the price for that day was $41. Then on 10/21, it needs to reference the last time it was changed, in this case $40 and use that value until it runs into another price change.

Example Data On Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]10/16[/TD]
[TD]10/17[/TD]
[TD]10/18[/TD]
[TD]10/19[/TD]
[TD]10/20[/TD]
[TD]10/21[/TD]
[TD]10/22[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]$38[/TD]
[TD]$30[/TD]
[TD]$30[/TD]
[TD]$30[/TD]
[TD]$41[/TD]
[TD]$40[/TD]
[TD]$40[/TD]
[/TR]
</tbody>[/TABLE]

We've tried the following formula and gotten pretty close:

Sheet1 = Original Data Set w/ repeats and blanks
Sheet2 = New sheet where data will be organized by date

=IF((SUMIF(Sheet1$A2:$BY2,">="&Sheet2AP$1,Sheet1$B2:$BZ2)-SUMIF(Sheet1$A2:$BY2,">="&Sheet2AQ$1,Sheet1$B2:$BZ2))=0,(IF(Sheet1$E2:$BY2<sheet2aq$1,offset(sheet1$e2,0,match(sheet2ap$1,sheet1$e2:$cq2,1)-1),sheet2$c2)),(averageifs(sheet1$b2:$bz2,sheet1$a2:$by2,">="&Sheet2AP$1,Sheet1$A2:$BY2,"<"&Sheet2AQ$1)))

But the match occasionally breaks down, it shows a value higher than we'd expect to see. I think that's because our array is technically not exactly in ascending order....but it can't be sorted.

Essentially, we need a way, to bring this data over to sheet 2, with the formula calculating averages for any days with more than one value, and filling in the previous day's values if there is no price change.

We can't clean up sheet 1 at all as there are 70K+ objects with all different assorted dates.

Let me know how we can do this! Thanks!</sheet2aq$1,offset(sheet1$e2,0,match(sheet2ap$1,sheet1$e2:$cq2,1)-1),sheet2$c2)),(averageifs(sheet1$b2:$bz2,sheet1$a2:$by2,">

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Would the two formulas shown below work?
Paste into B2 and copy down
Paste into C2 and copy across and down

Excel Workbook
	A	B	C	D	E	F	G	H
1	ID	Original Price	16-Oct	17-Oct	18-Oct	19-Oct	20-Oct	21-Oct
2	X	$40	$38	$30	$30	$30	$41	$41
3	Y	$50	$50	$50	$40	$40	$55	$55
Sheet

I've added the Original Price Column to Sheet2 in the event that an Object has no listings on the first day (16-Oct).
The first date formula can be modified to lookup the Original Price from Sheet1 if you prefer not to add that column.

Also note that the formulas assume you don't have any prices that would match the serial date values for your lookup dates.
Could you have any objects with prices in the $40,000-$50,000 range?

 

[katieni8]

Just wanted to say thank you for this! We ended up doing it a different way, I think probably clumsier.

=IF((SUMIF(Sheet1!$E2:$CQ2,">="&Sheet2!D$1,Sheet1!$F2:$CR2)-SUMIF(Sheet1!$E2:$CQ2,">="&Sheet2!E$1,Sheet1!$F2:$CR2))=0,(IF(Sheet1!$E2:$CQ2<Sheet2!E$1,OFFSET(Sheet1!$D2,0,MATCH(MAX(IF(Sheet1!$E2:$CQ2>=E$1,0),IF(Sheet1!$E2:$CQ2<E$1,Sheet1!$E2:$CQ2,0)),Sheet1!$E2:$CQ2,0)+1),Sheet2!$C2)),(AVERAGEIFS(Sheet1!$F2:$CR2,Sheet1!$E2:$CQ2,">="&Sheet2!D$1,Sheet1!$E2:$CQ2,"<"&Sheet2!E$1)))

 

[Jerry Sullivan]

Good to hear that you found a solution.

The formula you posted isn't complete- did you post the entire formula?

When posting, sometimes formulas get cropped after a "< " symbol due to the forum software interpretting that as a special character.
I'd be interested to see the whole formula if you will repost by adding a space after any "< " symbols.

 

[katieni8]

Oh! Great catch. That's exactly what happened. Here is the whole formula. Definitely let me know if you have any thoughts on it that you want to share - we kind of pieced it together after lots of trial and error.

=IF((SUMIF(Sheet1!$E2:$CQ2,"> ="&Sheet2!D$1,Sheet1!$F2:$CR2)-SUMIF(Sheet1!$E2:$CQ2,"> ="&Sheet2!E$1,Sheet1!$F2:$CR2))=0,(IF(Sheet1!$E2:$CQ2< Sheet2!E$1,OFFSET(Sheet1!$D2,0,MATCH(MAX(IF(Sheet1!$E2:$CQ2> =E$1,0),IF(Sheet1!$E2:$CQ2< E$1,Sheet1!$E2:$CQ2,0)),Sheet1!$E2:$CQ2,0)+1),Sheet2!$C2)),(AVERAGEIFS(Sheet1!$F2:$CR2,Sheet1!$E2:$CQ2,"> ="&Sheet2!D$1,Sheet1!$E2:$CQ2,"< "&Sheet2!E$1)))

Hopefully that will work. I put a space after all the < and > symbols.

Thanks!