Search code examples
excelformattingconditional-statements

How can I highlight a cell if its value is greater or lesser than the previous value from that column that's greater than 0?

I have an Excel spreadsheet where I record the current prices for some products I'm interested in. The headers are the product names and the index are timestamps, each cell contains a price if the product is in stock at the time, or "0" if it isn't.

Basically what I need is a formula I can apply to the entire spreadsheet that can compare the value of a cell to the most recent, non zero value from that same column, so that I can easily tell if prices have changed.

In the screenshot below I want the cell with the "4958.13" value to be filled red so I can tell the price for that product went up:

Enter image description here

I need one formula for price increase and another one for price drops.

Solution

  • Use formula in CF for price increments:

    =(LOOKUP(2,1/(A$1:A1>0),A$1:A1)<A2)*(A2<>0)

    for price decrements:

    =(LOOKUP(2,1/(A$1:A1>0),A$1:A1)>A2)*(A2<>0)*(SUM(A$1:A1)<>0)

    Apply to exact range.

    enter image description here