Making calculations based on non null cells to constant cell?

[JV63]

Hi. I have a file with a price in column Z for example and I need to compare that price to the nearest cell that has a price populated. So in other words if Cell Y has a price I want to compare Z to Y. If there is no price in Y but X has a price I need to compare Z to X and so on working backwards. There will always be a price in column Z but a price can show up anywhere in A through Y and I want to take the closest cell to Z that has a price to do my calculation. So working backwards.

I was thinking some kind of IF statement, but don't know how to write the formula as I have a lot of cells to look through.

In addition, is there a way to highlight the last cell that has a price, other than Z since that is my constant. I want to be able to show the user that last time there was a price for a certain product was in so and so month. Or highlight that cell in a color?

Thanks

 

[Ron Coderre]

This regular formula subtracts the last value in A9:Y9 from Z9

AA9: =Z9-LOOKUP(10^10,$A9:$Y9)

Is that something you can work with?

 

[JV63]

Thank you Ron. That was most helpful.

Is there a formula to flag or indicate when the prices have not changed? So for instance if A through Y are all the same price, flag it as a match or "same" or something like that?

Thanks again.

 

[JV63]

One other thing, with your formula: AA9: =Z9-LOOKUP(10^10,$A9:$Y9), is there also a way to display the column heading instead of the value that's in that cell. I need both ways actually.

Thanks

 

[JV63]

I got the heading thing to work by using nested IF so I'm ok with that unless there's a more efficient way of doing it?

Just need a formula then to flag when prices are all the same across the row.

Thanks

 

[Ron Coderre]

AA9: =LOOKUP(10^10,$A9:$Y9,$A1:$Y1)

Does that help?

 

[JV63]

AA9: =LOOKUP(10^10,$A9:$Y9,$A1:$Y1)

Does that help?

Awesome. Just had to use an extra $ with the A1:Y1 (AA9: =LOOKUP(10^10,$A9:$Y9,$A$1:$Y$1), otherwise if gave the actual values (prices).

This is great. If you can help me with the last piece, indicating when the prices are the same across the board for each row, that would be great.