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!