Price Band help for Retail Finance

[andysju]

I have a list of products and their respective price (by month). In the worksheet I have the products in column A and then months across the top and their prices are the data points.

I have already used an array function to count how many of the products fall within various price bands for every month. For example, 10 were less then $100, 20 were between $100-$200, etc...

Now I need to know how to tell which products moved from one price band to another over time. For example, in March 1 product may have been priced at $150 and then in December it is $90. Therefore, it moved down a price band.

I am dealing with a lot of products, therefore any help I can get would be appreciated. Thank You!!!

 

[Andrew Fergus]

Hi

I think you can get the effect you want using conditional formatting.

Assuming your data is set up such that you have the list of consecutive bands in say cells A34:A38 in a list format like 0, 100, 200, 300, 400 (much like a lookup table) then select the cell containing the first product but the 2nd month (in my instance cell C4), select menu option Format > Conditional Formatting :


Condition 1 : Formula Is : =LOOKUP(C4,$A$34:$A$38) < LOOKUP(B4,$A$34:$A$38), then pick a colour (like Red)<?XML:NAMESPACE PREFIX = LOOKUP(B4,$A$34 /><LOOKUP(B4,$A$34:$A$38), p (like colour a pick then Red)<></LOOKUP(B4,$A$34:$A$38),>
<LOOKUP(B4,$A$34:$A$38), p (like colour a pick then Red)<>Condition 2 : Formula Is : =LOOKUP(C4,$A$34:$A$38) > LOOKUP(B4,$A$34:$A$38), then pick another colour (like Green)

Ok. The copy > paste special > formats over the rest of the data. This will highlight products who dropped a band Red and those that jumped up a band Green. Do not apply the conditional formats to the first months column, and make sure the band range starts with a 0 per my example. Modify the cell references to suit.

HTH, Andrew
</LOOKUP(B4,$A$34:$A$38),>