# 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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

##### 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
5
Views
143
Replies
6
Views
124
Replies
2
Views
189
Replies
4
Views
122
Replies
0
Views
457

### Forum statistics

1,141,757
Messages
5,708,355
Members
421,566
Latest member
7Nabisco ### 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