Find Jobs
Hire Freelancers

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.
Project ID: 8720324

About the project

8 proposals
Remote project
Active 8 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
Awarded to:
User Avatar
Hello Sir, Ready to start right now and finish ASAP. Pay me only when your job is 100% done. Thanks, Jay
$50 CAD in 0 day
5.0 (590 reviews)
8.3
8.3
8 freelancers are bidding on average $82 CAD for this job
User Avatar
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.
$94 CAD in 1 day
5.0 (141 reviews)
7.1
7.1
User Avatar
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
$94 CAD in 3 days
4.9 (38 reviews)
5.6
5.6
User Avatar
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.
$123 CAD in 3 days
4.9 (28 reviews)
4.5
4.5
User Avatar
A proposal has not yet been provided
$105 CAD in 3 days
5.0 (9 reviews)
4.2
4.2
User Avatar
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.
$35 CAD in 3 days
4.3 (5 reviews)
2.8
2.8
User Avatar
A proposal has not yet been provided
$55 CAD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
Aucune proposition n'a encore été fournie.
$100 CAD in 3 days
0.0 (0 reviews)
0.0
0.0

About the client

Flag of CANADA
Edmonton, Canada
5.0
3
Payment method verified
Member since Oct 19, 2015

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.