Create Dynamic Formula Based on Changing Timestamps and Depths
$30-250 CAD
Completed
Posted over 8 years ago
$30-250 CAD
Paid on delivery
Please see workbook, note some columns hidden for ease of seeing all the relevant columns.
Some of the logic is there already, but is not operating correctly.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, LR As Long
LR = Range("A" & [login to view URL]).End(xlUp).Row
For Each cell In Target
[login to view URL] = False
If [login to view URL] = LR Then
With Range("A" & LR).EntireRow
.Copy Range("A" & LR + 1)
On Error Resume Next
.Offset(1).SpecialCells(xlConstants).ClearContents
End With
Range("M" & LR + 1).FormulaR1C1 = "=RC4"
[login to view URL] = "$A$1:$V$" & LR + 1
End If
If [login to view URL] = 7 Then 'if column G and AK is blank, add a new timestamp
If Range("AK" & [login to view URL]) = "" Then
Range("AK" & [login to view URL]) = Now
If DateDiff("n", (Range("AK" & Range("BL" & [login to view URL]).End(xlUp).Row).Value), (Range("AK" & [login to view URL]).Value)) >= 10 Then
Range("BK13").Formula = "=(E" & [login to view URL] & "-E" & Range("BL" & [login to view URL]).End(xlUp).Row & ")/((AK" & [login to view URL] & "-AK" & Range("BL" & [login to view URL]).End(xlUp).Row & ")*24)"
Range("BL" & [login to view URL]) = "Flag"
End If
End If
End If
[login to view URL] = True
Next cell
End Sub
You will need to open workbook for this to make sense. The "Slide Sheet" tab.
The equation should be dynamic and be =(E?-E?)/((AK?-AK?)*24) with the question marks being the dynamic variable (This is in cell BK13). so for any time stamp (AK) that is greater than 10 mins the formula needs to choose the variable. So, for instance, if the sheet stopped at row 18 and there were currently no rows filled in below it would be subtracting 444(E18)-434 (E17) and the corresponding time stamps (AK18 and AK17). Because there is more than 10 mins between the time stamps (AK18 and AK17) Currently it is taking 444 (E18)-414 (E15) and its corresponding time stamps (AK18 and AK15) (but seems to be broken).
It also has a similar mistake when going the other way.
So in this one it is using the 11:22 (AK19) and the 11:00 (AK18) time stamps, when it should actually just be using the last (or current) one (11:23) (AK21) and the 11:00 (AK18). So should be subtracting 474 (E21)-444 (E18), not 454 (E19)-444 (E18) and of course using the corresponding time as well (AK21 and AK18)
Also
Can you put in error checking for that formula so that if #ref error (like when starting a new workbook) is just a blank please.
I am mostly online. Just drop me a message so that we can discuss further about pricing and job details.
I have a 6 years of business analyst experience in a global company and 3 years data scientist experience in a game company before my freelancer career. Excel is my legs and arms. I can make any reporting, formulas, pivot tables, macros etc.
I am mostly online. Just drop me a message so that we can discuss further about pricing and job details.
Hi,
I am working as Lead Analyst in a Fortune 500 MNC. I have worked on market analysis, segmentation, market sizing, ACBC (conjoint) surveys, pricing models, income statements, business analysis etc.
Prior that I have completed MBA and bachelors in Engineering.
I have worked extensively on excel and VBA in my job and here on freelancer as well.
I believe I can help in your work. Please have a look at reviews provided by previous employers. Please contact for further discussion.
Regards!
Vikas
I am very interested in your project.I will do it quickly. I have 5+ years exp. in MS Office, Designing, Quick-books, Office Management, PDF, Google spreadsheet, Vba and typing. I would like to thank you in advance.
Hello. I'm understand how to do this job. Skills in VBA, experiance of working in excel, access two years. Can do a dashbord for this project.
Good luck and succeed in this project.