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 4478 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
Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

Similar discussions

Code for macro that conditionally inserts rows
bfleming -
bfleming -

4 responses
Excel Macro - Set and autofill a range?
KT -
excel987 -

28 responses
Converting rows of data in notepad to columns
Need help -
Philgood_FR -

2 responses
How to generate a STATIC unique identifier for each Excel row
Musicar -
ac3mark -

4 responses