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

Last value in a row with diff. 25% on rel val
Oracle date diff - Guide
Saints row 2 cheats - Guide
How to delete a row in a table in word - Guide
Canoscan lide 25 driver windows 10 - Download - Drivers
Pinnacle studio 25 download - Download - Video editing

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

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

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

Similar discussions

Code for macro that conditionally inserts rows

whatsapp businness do not send away message (most of the time)

Converting rows of data in notepad to columns

Similar discussions

Subscribe To Our Newsletter!