Array formula to determine changes in price and time of changes

[Legacy 332279]

Hey!

I've been looking around for a while in the forums, but haven't been able to find a solution. I'm working on an array formula that is supposed to determine when a price of an item has changed and what the new price is based on an inventory log that contains several different items. I've got the core functionality down, but I now struggle with two things:

1) I would like to omit all false values from the result, so that the resulting list doesn't contain any blank cells. I've tried using the solution provided by Aladin in this post, but I can't wrap my head around how to incorporate my array formula into that solution.

2) I would also like for the formula to include the first first registered price of an item, which the current formula omits, but again I'm unsure as to how.

This is the current array formula:

{=IF((INDEX(I:I,SMALL(IF(COUNTIF(Q3,A1:A10),ROW(A1:A10)-MIN(ROW(A1:A10))+1),ROW(INDIRECT("1:30"))))<>INDEX(I:I,SMALL(IF(COUNTIF(Q3,A1:A10),ROW(A1:A10)-MIN(ROW(A1:A10))+1),ROW(INDIRECT("2:30"))))),(INDEX(G:I,SMALL(IF(COUNTIF(Q3,A1:A10),ROW(A1:A10)-MIN(ROW(A1:A10))+2),ROW(INDIRECT("1:30"))),{1,3})))}

In essence the parent IF function checks if a row that contains data on an item (determined by an ID-number in Q3) has a different price than the previous row of data on the same item. If this is true, then it returns the time of the change (from column G) and the new price (from column I). If false, it simply returns false.

The following is the test sheet I've been using to create what I have so far (without irrelevant information):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]V[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]01-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]01-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]90[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]02-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]02-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]200[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]02-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]90[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]02-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]15-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]15-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]15-01-15[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]80[/TD]
[/TR]
</tbody>[/TABLE]


Then, say if Q3 is set to 101, the following is a comparison of the current result and the desired result:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"] Current result[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] Desired result[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-01-15[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15-01-15[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD="align: center"]15-01-15[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]#NUM![/TD]
[TD="align: center"]#NUM![/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated!

 

[Legacy 332279]

Ok, so I figured out how to include the first registered line of data by changing the countif-function to accept both the ID number and the text string "ID".

I'm still no closer to a solution on my first issue though.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]ID[/td][td]N[/td][td]Q[/td][td]P[/td][td]S[/td][td]M[/td][td]T[/td][td]Q[/td][td]V[/td][td][/td][td]ID[/td][td]

101​

[/td][/tr]

[tr][td]

2​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/1/2015​

[/td][td][/td][td]

50​

[/td][td][/td][td]date[/td][td]price[/td][/tr]

[tr][td]

3​

[/td][td]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/1/2015​

[/td][td][/td][td]

90​

[/td][td][/td][td]

1/1/2015​

[/td][td]

50​

[/td][/tr]

[tr][td]

4​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

50​

[/td][td][/td][td]

1/15/2015​

[/td][td]

60​

[/td][/tr]

[tr][td]

5​

[/td][td]

103​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

200​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

50​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/15/2015​

[/td][td][/td][td]

60​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/15/2015​

[/td][td][/td][td]

60​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/15/2015​

[/td][td][/td][td]

80​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

A2:A10 is named id, G2:G10 date, and I2:I10 price.

Moreover, Ivec is defined using Formulas | Name Manager as referring to:

=ROW(price)-ROW(INDEX(price,1,1))+1

L1: 101 (An ID of interest.)

K3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(date,SMALL(IF(FREQUENCY(IF(price<>"",
    IF(id=$L$1,MATCH(price,price,0))),Ivec),Ivec),ROWS(K$2:K2))),"")

L3, just enter and copy down:

=IF(K3="","",INDEX(price,MATCH($K3,date,0)))

Question. Do we need to list 18-Jan-2015 if the price falls back to 50?

 

[Legacy 332279]

Thank you Aladin! A much more elegant formula than mine.

Yes, unfortunately prices can go both ways, so the formula would have to pick up that as well. Do you see any good solutions for incorporating that?

 

[Legacy 332279]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]N[/TD]
[TD]Q[/TD]
[TD]P[/TD]
[TD]S[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]Q[/TD]
[TD]V[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]

101​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/1/2015​

[/TD]
[TD][/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD]date[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

102​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/1/2015​

[/TD]
[TD][/TD]
[TD]

90​

[/TD]
[TD][/TD]
[TD]

1/1/2015​

[/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/2/2015​

[/TD]
[TD][/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD]

1/15/2015​

[/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/2/2015​

[/TD]
[TD][/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

102​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/2/2015​

[/TD]
[TD][/TD]
[TD]

90​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/2/2015​

[/TD]
[TD][/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/15/2015​

[/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/15/2015​

[/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

102​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1/15/2015​

[/TD]
[TD][/TD]
[TD]

80​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


A2:A10 is named id, G2:G10 date, and I2:I10 price.

Moreover, Ivec is defined using Formulas | Name Manager as referring to:

=ROW(price)-ROW(INDEX(price,1,1))+1

L1: 101 (An ID of interest.)

K3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(date,SMALL(IF(FREQUENCY(IF(price<>"",
    IF(id=$L$1,MATCH(price,price,0))),Ivec),Ivec),ROWS(K$2:K2))),"")

L3, just enter and copy down:

=IF(K3="","",INDEX(price,MATCH($K3,date,0)))

Question. Do we need to list 18-Jan-2015 if the price falls back to 50?

I've discovered a major problem with the formula. If you you change the value 60 in I8 (in the test sheet above) to 90, the formula will result in showing the price change as happening at 1/1/2015 instead of 1/15/2015 as it should. That is to say, the formula retrieves the date from the third row, which contains data from ID 102, instead of the eighth.

 

[Aladin Akyurek]

I've discovered a major problem with the formula. If you you change the value 60 in I8 (in the test sheet above) to 90, the formula will result in showing the price change as happening at 1/1/2015 instead of 1/15/2015 as it should. That is to say, the formula retrieves the date from the third row, which contains data from ID 102, instead of the eighth.

You are right about that. Plus we got a more funtamental problem: The same price appearing for the same id on a later date. See whether the following set up satisfies...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]ID[/td][td]N[/td][td]Q[/td][td]P[/td][td]S[/td][td]M[/td][td]T[/td][td]Q[/td][td]V[/td][td]Eval[/td][td]ID[/td][td]

101​

[/td][/tr]

[tr][td]

2​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/1/2015​

[/td][td][/td][td]

50​

[/td][td]

50​

[/td][td]count[/td][td]

4​

[/td][/tr]

[tr][td]

3​

[/td][td]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/1/2015​

[/td][td][/td][td]

90​

[/td][td]

90​

[/td][td]date[/td][td]price[/td][/tr]

[tr][td]

4​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

50​

[/td][td]#[/td][td]

1/1/2015​

[/td][td]

50​

[/td][/tr]

[tr][td]

5​

[/td][td]

103​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

200​

[/td][td]

200​

[/td][td]

1/15/2015​

[/td][td]

90​

[/td][/tr]

[tr][td]

6​

[/td][td]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

90​

[/td][td]#[/td][td]

1/15/2015​

[/td][td]

60​

[/td][/tr]

[tr][td]

7​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/2/2015​

[/td][td][/td][td]

50​

[/td][td]#[/td][td]

1/30/2015​

[/td][td]

50​

[/td][/tr]

[tr][td]

8​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/15/2015​

[/td][td][/td][td]

90​

[/td][td]

90​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/15/2015​

[/td][td][/td][td]

60​

[/td][td]

60​

[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/15/2015​

[/td][td][/td][td]

80​

[/td][td]

80​

[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1/30/2015​

[/td][td][/td][td]

50​

[/td][td]

50​

[/td][td][/td][td][/td][/tr]
[/table]

J2, control+shift+enter, not just enter, and copy down:

=IF(ISNA(MATCH(I2,$J$1:J1,0)),I2,
   IF(ISNA(MATCH(I2,$J$1:J1,0))*ISNA(MATCH($A2&"|"&$G2,$A$1:A1&"|"&$G$1:G1,0)),I2,
   IF(ISNUMBER(LOOKUP(9.99999999999999E+307,1/($A$1:A1=A2),$I$1:I1)),
   IF(LOOKUP(9.99999999999999E+307,1/($A$1:A1=A2),$I$1:I1)<>I2,I2,"#"),"#")))

L2, control+shift+enter, not just enter:

=SUM(IF(A2:A11=L1,IF(ISNUMBER(J2:J11),1)))

K4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($K$2:K2)<=$L$2,INDEX($G$2:$G$11,SMALL(IF($A$2:A11=$L$1,
   IF(ISNUMBER($J$2:$J$11),ROW($G$2:$G$11)-ROW($G$2)+1)),ROWS($K$2:K2))),"")

L4, control+shift+enter, not just enter, and copy down:

=IF($K4="","",INDEX($J$2:$J$11,SMALL(IF($A$2:$A$11=$L$1,
   IF($G$2:$G$11=$K4,ROW($J$2:$J$11)-ROW($J$2)+1)),
   COUNTIFS($K$4:K4,K4))))

 

[Legacy 332279]

This seems to work perfectly, thank you! I would certainly have given up if not for this.

 

[Aladin Akyurek]

This seems to work perfectly, thank you! I would certainly have given up if not for this.

You are welcome.