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!
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!