# difficult one

#### jpowell79

##### Active Member
Hi Guys,

This one is VERY complicated.

I have column R, where numbers generally increase as you move down the column

e.g.

r1=5
r2=7
r3=11
r4=19

etc.

Now on occasion the the number will suddenly decrease. What I need to do is compare the FINAL non-blank cell in column R to the value IMMEDIATELY BEFORE the last time the value dropped in the column.

This make much more sense with a few examples For example:

r1=5
r2=7
r3=11
r4=21
r5=13 (value drops here)
r6=16
r7=19 (final non-blank value)

Now the value just before the figures drop is in cell r4=21
The final value is r7=19. so the value I'm looking for is 19-21 =-2

Here are a couple more examples:

r1=8
r2=12
r3=16
r4=24
r5=11 (value drops here)
r6=13
r7=15
r8=17 (final non-blank value)

Now the value just before the figures drop is in cell r4=24
The final value is r8=17. so the value I'm looking for is 17-24 =-7

one more example:

r1=8
r2=13
r3=18
r4=14 (value drops here)
r5=16
r6=19
r7=26
r8=29
r9=22 (value drops here)
r10=25
r11=28
r12=33
r13=39
r14=31 (value drops here)
r15=32
r16=33
r17=44 (final non-blank value)

Now the value just before the figures drop is in cell r13=39
The final value is r17=44. so the value I'm looking for is 44-39 =5

Hope this makes sense! ### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

##### Well-known Member
Hello,

no idea why i did a VB version

Code:
``````Sub check_numbers()
MY_LAST_CELL = Range("R" & Range("R65536").End(xlUp).Row).Value
For MY_ROWS = Range("R65536").End(xlUp).Row To 1 Step -1
If Range("R" & MY_ROWS - 1).Value > Range("R" & MY_ROWS).Value Then
MY_RESULT = MsgBox("Your value is " & MY_LAST_CELL - Range("R" & MY_ROWS - 1).Value, vboknly, "VALUE")
Exit Sub
End If
Next MY_ROWS
End Sub``````

#### barry houdini

##### MrExcel MVP
Assuming you have up to 100 numbers in column R, adjust upwards if necessary

=LOOKUP(9.99999999999999E+307,R1:R100)-LOOKUP(2,1/((R2:R100< R1:R99)*(R2:R100<>"")),R1:R99)

#### Peter_SSs

##### MrExcel MVP, Moderator
jpowell79

Here is another alternative using a 'Helper' column.

1. In S1 (copied down): =IF((R1>R2)*(R2<>""),1,"")
2. U2: =LOOKUP(9.99999999999999E+307,R:R)-LOOKUP(9.99999999999999E+307,S:S,R:R)
Mr Excel.xls
QRSTUV
18 Result
213 5
3181
414
516
619
726
8291
922
1025
1128
1233
13391
1431
1532
1633
1744
18
jpowell79

#### jpowell79

##### Active Member
works a treat barry! you've saved me yet again! thanks mate #### mortgageman

##### Well-known Member
Peter, Barry: I know I will be embarrased when you point out how what I missed, but where does the 307 come from???????

Gene, "The Mortgage Man", Klein

#### barry houdini

##### MrExcel MVP
Hi Gene,

from Excel Help, excel specifications and limits

Largest number allowed to be typed into a cell 9.99999999999999E+307

#### mortgageman

##### Well-known Member
Well call me pyschic and stomp my feet. I was embarrassed

Gene, "The Mortgage Man", Klein

Replies
0
Views
125
Replies
5
Views
224
Replies
1
Views
2K
Replies
4
Views
214
Replies
6
Views
228

### Forum statistics

1,176,538
Messages
5,903,655
Members
435,041
Latest member
Burleyg ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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