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

#### mpierce

##### New Member
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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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?

Here is a User Defined Function that should do that:
Code:
``````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)

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

Replies
1
Views
987
Replies
9
Views
940
Replies
5
Views
785
Replies
0
Views
442
Replies
0
Views
885

1,207,259
Messages
6,077,348
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back