Find Jobs
Hire Freelancers

Create Dynamic Formula Based on Changing Timestamps and Depths

$30-250 CAD

已完成
已发布超过 8 年前

$30-250 CAD

货到付款
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.
项目 ID: 8720324

关于此项目

8提案
远程项目
活跃8 年前

想赚点钱吗?

在Freelancer上竞价的好处

设定您的预算和时间范围
为您的工作获得报酬
简要概述您的提案
免费注册和竞标工作
颁发给:
用户头像
Hello Sir, Ready to start right now and finish ASAP. Pay me only when your job is 100% done. Thanks, Jay
$50 CAD 在0天之内
5.0 (590条评论)
8.3
8.3
8威客以平均价$82 CAD来参与此工作竞价
用户头像
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 在1天之内
5.0 (141条评论)
7.1
7.1
用户头像
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 在3天之内
4.9 (38条评论)
5.6
5.6
用户头像
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 在3天之内
4.9 (28条评论)
4.5
4.5
用户头像
A proposal has not yet been provided
$105 CAD 在3天之内
5.0 (9条评论)
4.2
4.2
用户头像
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 在3天之内
4.3 (5条评论)
2.8
2.8
用户头像
A proposal has not yet been provided
$55 CAD 在3天之内
0.0 (0条评论)
0.0
0.0
用户头像
Aucune proposition n'a encore été fournie.
$100 CAD 在3天之内
0.0 (0条评论)
0.0
0.0

关于客户

CANADA的国旗
Edmonton, Canada
5.0
3
付款方式已验证
会员自10月 19, 2015起

客户认证

谢谢!我们已通过电子邮件向您发送了索取免费积分的链接。
发送电子邮件时出现问题。请再试一次。
已注册用户 发布工作总数
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
加载预览
授予地理位置权限。
您的登录会话已过期而且您已经登出,请再次登录。