difficult one

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
336
Office Version
  1. 2021
Platform
  1. Windows
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
works a treat barry! you've saved me yet again! :)

thanks mate :)
 
Upvote 0
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
 
Upvote 0
Hi Gene,

from Excel Help, excel specifications and limits

Largest number allowed to be typed into a cell 9.99999999999999E+307
 
Upvote 0
Well call me pyschic and stomp my feet. I was embarrassed

Gene, "The Mortgage Man", Klein
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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?

Disable AdBlock

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
Back
Top