Pricing Differences

[bhanjic]

PRODUCT JAN FEB MARCH P1 P2 DIF

A $100 $250 $0 $250 $100 $150

B $0 $200 $100 $100 $200 -$100

C $100 $0 $400 $400 $100 $300


Hi friends,

This is an example of pricing differences by products and by months.

The objective of this report is to calculate pricing differences from the latest prices.

On the product A for example the 1st latest price occured in Feb with an amount of $250 P1), before that the latest occurance was in jan with 100$ (p2) and the difference is 150$.

The objective is to walk from the current date to the past and find the last 2 prices and calculate the difference.

Can someone help me to build a formula for P1 and P2?


Thanks in advance

CB

 

[Luke M]

P1 array formula:

=INDEX(2:2,,LARGE(IF(B2:D2>0,COLUMN(B2:D2)),1))


P2 array formula:

=INDEX(2:2,,LARGE(IF(B2:D2>0,COLUMN(B2:D2)),2))


Remember that array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.

 

[Hui]

CB


Welcome to the Chandoo.org Forums


In

E2: =INDEX(B2:D2,,SUMPRODUCT(LARGE((COLUMN(B2:D2)*(B2:D2<>0)),1))-1)

F2: =INDEX(B2:D2,,SUMPRODUCT(LARGE((COLUMN(B2:D2)*(B2:D2<>0)),2))-1)


Copy the 2 cells down