Hi,
I have been struggling with trying to create a spread sheet either through multiple formulas in various cells or creating a VBA to provide an output based on input from the use of Power Query. Im trying to create a program that will allow me to calculate the depth of a sensor that is attached onto an apparatus that allows the sensor to slide back and forth up to 10 ft before reaching its limit of travel. This apparatus is moved through the use of PVC pipe that can vary in distance based upon the user input. The part that I'm having a problem with is creating the formula that would include the following issue: As the tool is pushed or pulled through the hole, the sensor will not move until it has met the limit of travel in either direction. The problem is writing the formula that will know when to either subtract/add distance based on its previous state. In my progress to write this, I can have my power query provide me the position (amount of pvc inputted) in 15 sec intervals so I can the most recent input and the previous input to calculate the change in movement. Its then taking this change and determining if the sensor would have moved and whats its new depth. I have attached an file with a few images trying show what I am explaining. I have had problems trying to write this as formulas in the regular cells and don't know if there is an easy way to write this as formula in VBA/Macro form.
Input-Output.jpg
Thanks,
Brandon
Bookmarks