Comparing prices in a row and finding how much it went up

[mpierce]

Hi guys, I have some prices within rows like:


A B C D E F
row1 $1.02 $1.05 $1.07


So, if the price changed, the price appears in the cell, if there was no change then nothing appears in the cell. The prices and go up or down.

Now what I need to do is write a macro (or maybe make a user defined function) that will find the most recent price change (meaning that a price appears in the most recent cell, and then finds the nect most recent cell in which a price appears and calculates the difference.

Clarification guys:

Many of the cells contain nothing at all. My little example that I drew doesn't look very good.

If there is no price change then nothing apears in the cell.

So maybe $1.03 in cell 1A, then nothing in 1B then $1.01 in 1C, then nothing in 1D. I also only need to compare the two most recent prices.





For example, in the above it would be $0.02.

I would also like to be able to show positive an negative like +$0.03 or -$0.04.


Thank you all very much in advance for your help!!!

 

[euclid1179]

When you say "most recent price" are you implying that the column headings use dates?

Maybe you can copy a picture of the spreadsheet you're creating?

 

[Joe4]

Here is a User Defined Function that should do that:

Function PriceChange(myRange As Range) As Variant

    Dim cell As Range
    Dim LastValue As Double
    Dim NextToLastValue As Double
    Dim CellCount As Long
    
    For Each cell In myRange
        If Len(cell) > 0 Then
            CellCount = CellCount + 1
            If CellCount > 1 Then NextToLastValue = LastValue
            LastValue = cell.Value
        End If
    Next cell
    
    If CellCount > 1 Then
        PriceChange = LastValue - NextToLastValue
''        If PriceChange = 0 Then PriceChange = ""
    Else
        PriceChange = ""
    End If
    
End Function

Load it into a module, then use it like any other Excel function.

Simply enter the whole possible range for each row, i.e. for row 1:
=PriceChange(A1:G1)

 

[mpierce]

Hi Euclid,

I cant figure out hoe to post a darn picturem but yes the columns use dates

April May June

for each month there are three cells (3 columns) : 1) a quantity 2) a price and then 3) a total spent (quantity times price).

If nothing was bought that month, then all 3 cells are empty

I need two things:

1) If the price has between the current purchase and the most most recent purchase, I need to know what that change was.

2) I also need to know how much the price changed sinje January 2005 (the past 10 months

Hope that clears it up