Previous Topic Next Topic

Last value in a row with diff. 25% on rel val

Solved/Closed
mattkatt
Posts
2
Registration date
Friday March 5, 2010
Status
Member
Last seen
March 5, 2010
 - Mar 5, 2010 at 02:15 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Mar 5, 2010 at 03:47 PM
This is tough to even state, but I'll try with an example

Example: find the first value that changed 50% in relation to the last time when there was a change of 50%, beggining when the value is not zero.

A B C D E F G H

0 0 10 12 15 17 20 19

The first time the value changed 50% is in E, but then it changed again in G, so I need a formula that returns 20; if there was no 20, the correct answer would be 15

2 replies

Answer 1 / 2
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Mar 5, 2010 at 02:40 PM
How 20 is 50% change from 15

10 * 1.5 = 15
15 * 1.5 = 22.5
0
Answer 2 / 2
mattkatt
Posts
2
Registration date
Friday March 5, 2010
Status
Member
Last seen
March 5, 2010
Mar 5, 2010 at 02:51 PM
yes, that would be 22.5, small mistake when setting the example
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Mar 5, 2010 at 03:47 PM
Assumption

1. Data is on row 1
2. There is no blank cell between data
3. It will return zero also, if that is the case as 1.5 of 0 is 0


Call the function as =lastFifty() 


Public Function lastFifty() As Variant

Dim objNum As Object 'hold number and its 1.5 times value
Dim vNum As Variant 'current number
Dim lastValue As Variant 'last 50 found
Dim col As Integer ' start column

Set objNum = CreateObject("Scripting.Dictionary")

col = 1
lastValue = ""

vNum = Trim(Cells(1, col))

Do While (vNum <> "")
        
    If Not (objNum.exists(CStr(vNum * 1.5))) Then
        
        objNum.Add CStr(vNum * 1.5), vNum
    
    End If
    
    If (objNum.exists(CStr(vNum))) Then
        lastValue = vNum
    End If
    
    col = col + 1
    vNum = Trim(Cells(1, col))

Loop

lastFifty = lastValue

End Function
0