difficult one

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331
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.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
May 28, 2005
Messages
50,670
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 20, 2006
Messages
331

ADVERTISEMENT

works a treat barry! you've saved me yet again! :)

thanks mate :)
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Jun 30, 2005
Messages
2,015
Well call me pyschic and stomp my feet. I was embarrassed

Gene, "The Mortgage Man", Klein
 

Forum statistics

Threads
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.
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
Top