Find Jobs
Hire Freelancers

Macro for Excel

$30-100 USD

Completed
Posted over 11 years ago

$30-100 USD

Paid on delivery
Hi, I am looking to create a simple VBA macro for Excel but have no programming skills beyond simple formulae. I have a spreadsheet that receives measurement data from an external source. This data populates column A and automatically updates every second by adding the latest value to the next row in column A. All data previously populated remains unchanged. The macro needs to look for 2 scenarios and return a flag (different for each scenario). Full details are set out below. James ## Deliverables Hi, I am looking to create a simple VBA macro for Excel but have no programming skills beyond simple formulae. I have a spreadsheet that receives measurement data from an external source. This data populates column A and automatically updates every second by adding the latest value to the next row in column A. All data previously populated remains unchanged. The macro needs to look for 2 scenarios and return a flag (different for each scenario): The first scenario is that the latest measurement (+/- 1 increment) (called the "Latest Value") equals the lowest measurement (called the "Trigger Value") in the preceding X number of values (called the "Lookback Period") and in between these two measurements the measurements had increased to X increments above (called the "Critical Level") the Trigger Value before hitting the same level again at the Latest Value. The macro should also check that the subsequent X values (known as the "Following Period") increase more than X increments (known as the "Danger Level") from the Latest Value before returning the flag. The flag can be as simple as returning a "1" in a cell. The second scenario is a mirror image of the first scenario. The latest measurement (+/- 1 increment) (ie the Latest Value) equals the highest measurement (the Trigger Value) in the preceding X number of values (the Lookback Period) and in between these two measurements the measurements had decreased to X increments below (the Critical Level) the Trigger Value before hitting the same level again at the Latest Value. The macro should also check that the subsequent X values (the Following Period) decrease more than X increments (the Danger Level) from the Latest Value before returning the flag. The flag can be as simple as returning a "0" in a cell, but the cell used should be different from the first scenario. The sheet contains some code defining the increments because the size of the increments varies according to the level of measurement. For example, below 2.0 increments are 0.01 but at 2.0 and above increments are 0.02. This code will be provided to the successful bidder. In all cases X should be definable by the user entering the value in another cell and the macro should not trigger until column A contains at least the same number of measurements as the Lookback Period. Explaining a bit more about the measurements may help to understand the purpose of the macro. The measurements are engineering tolerances derived from testing metals in a laboratory using our own incremental scale. Once the highest or lowest measurement has been recorded (ie the Trigger Value) we then need to look for a situation where the measurements go up or down to the Critical Level before going back to the Trigger Value on the Latest Value and then continuing to the Danger Level in the Following Period. On a line chart it looks like there is an invisible line drawn horizontally from the Trigger Value and this acts like a ceiling or a floor (depending on whether the Trigger Value was the highest or lowest value in the Lookback Period) so that the measurements may not go past this level (+/- 1 increment) and the line just looks like it is bouncing off the ceiling/floor and not able to penetrate through. On occasion it will penetrate through and that is why we need to ensure the Danger Level is reached during the Following Period to ensure this does not happen. Once the macro has checked the Danger Level has not been reached we need the flag to appear for a few seconds to show that the criteria has been met whilst the macro carries on monitoring the Lookback Period for the next time one of the two scenarios occurs. For the subsequent scenarios it may be that the previous Latest Value becomes the next Trigger Value. Once the criteria are met the line chart will look like either a W or an M depending on whether the Trigger Value is the highest or lowest in the Lookback Period. The W shape occurs when the Trigger Value is the lowest value in the Lookback Period and the Latest Value equals the Trigger Value. The first of the bottom points of the W represents the Trigger Value, the top point in the middle represents the Critical Level, the second of the bottom points is the Latest Value and the final diagonal of the W would be the Danger Level. When the line on the chart looks like a W it looks like the bottom two points are bouncing of an invisible floor. The M shape occurs when the Trigger Value is the highest value in the Lookback Period and the Latest Value equals the Trigger Value. It looks like a mirror image of the W scenario. The attached Excel file contains a line chart showing how the "W" scenario looks in graphical form. Ignore the fact that the data is in column B, this file is just to illustrate the example. So in the "W" scenario: 1. Lookback Period is set by the user to 30 2. The macro determines that the lowest value during the Lookback Period (the Trigger Value) is 2.0 3. Critical Level is set by user to 5 increments (which at the 2.0 level would be 0.02 per increment or 0.10 for 5 increments) 4. Following Period is set by the user to 4 measurements 5. Danger Level is set by the user to 3 increments (which at the 2.0 level would be 0.02 per increment or 0.6 for 3 increments) The Trigger Value of 2.0 was the lowest value and was recorded at measurement number 100, the Critical Level of 2.10 (2.0 + 0.1) was exceeded at measurement number 110 and at measurement number 120 the Latest Value is 2.0. In the Following Period the Danger Level of 2.06 is reached so the macro can return the flag. If anyone has any questions please ask. James
Project ID: 2776470

About the project

9 proposals
Remote project
Active 12 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
See private message.
$30 USD in 14 days
4.8 (9 reviews)
3.0
3.0
9 freelancers are bidding on average $103 USD for this job
User Avatar
See private message.
$200.60 USD in 14 days
4.9 (176 reviews)
6.4
6.4
User Avatar
See private message.
$80.75 USD in 14 days
4.8 (16 reviews)
4.4
4.4
User Avatar
See private message.
$80.75 USD in 14 days
4.6 (4 reviews)
3.4
3.4
User Avatar
See private message.
$50 USD in 14 days
5.0 (4 reviews)
1.6
1.6
User Avatar
See private message.
$212.50 USD in 14 days
0.0 (0 reviews)
0.0
0.0
User Avatar
See private message.
$99.45 USD in 14 days
0.0 (0 reviews)
0.0
0.0
User Avatar
See private message.
$80.75 USD in 14 days
0.0 (0 reviews)
0.0
0.0
User Avatar
See private message.
$90 USD in 14 days
0.0 (0 reviews)
0.5
0.5

About the client

Flag of UNITED KINGDOM
Leeds, United Kingdom
5.0
23
Payment method verified
Member since Aug 15, 2012

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.