Complicated IF statement contraction

Hi All, I have a tricky question that I am hoping a more experienced excel user may be able to assist. This might not be the right spot, or even the right forum, let me know if this is the case or where i might be able to find some assistance.

First off, a bit of a background to the problem.

I have an engineering problem that I have solved already (sort of) using excel, but I am trying to compact the design to use fewer cells. It is a friction/energy problem - i know the kinetic energy of an object and i am working out when it will stop moving. The energy is absorbed and converted to heat/sound etc. The amount of energy absorbed per meter changes after a given distance.

As an example, this is what a graph would look like:
Attachment 608805 (can't upload this one, but you get the idea in black below)
I am working out the area under the curve and then (for a given value) determining the distance at which all energy is absorbed. From this example, an energy of 32kJ would sit at 0.95m.

I was working this out by calculating this energy for a given distance (say every 0.1m) and working out the difference given the kJ/m between the two distances. Easy enough.

I have since simplified that to work out the energy between the step values instead, reducing it from ~thousand cells (given the range of distance i needed to cover, along with different starting energy levels i had around 10x100 cells), down to this:
=MAX(
(B6/D$5)*IF(B6<$D$3,1,0),
(($B6-$D$3+$D$2*$E$5)/$E$5)*(IF($B6<=$E$3,1,0)*IF($B6>$D$3,1,0)),
(($B6-$E$3+$E$2*$F$5)/$F$5)*(IF(B6<=$F$3,1,0)*IF(B6>$E$3,1,0)),
(($B6-$F$3+$F$2*$G$5)/$G$5)*(IF(B6>$F$3,1,0))
)

Cells 1.png

Much easier to explain to someone in this format and develop a standalone app, as well as being able to add other functions. Which is where the trouble starts...

I've added things like putting the object on a slope (recursive check, not too complicated) and other additional variables, but the tricky one is adding additional sets of elements at variable distances from the first point of contact.

Adding items with variable offsets would look like this:
Graph 2.png
Graph 3.png
Graph 4.png

Now, previously i have succeeded in doing this by offsetting them by a fixed distance (say 0.1m), with up to 10 additional elements, and this resulted in a 10x100 table that could be summed into a single row and the appropriate energy/distance extracted (a little more complicated than that, but not by much).

Now, i want to be able to do this, but instead of having fixed offsets, i would like to be able to vary them. I could do this the same way, just increasing the resolution of each step (needing 1000's of columns, which can be done by not a lot of copy/paste/drag) and performing the same lookup. But i would like to know if it is possible to do this in a mathematical way (other than a "finite element analysis" method).

I would need to be able to use at least 6 additional elements, which straight up makes the number of IF statements to break it up into pieces a hard thing to do by hand (with each step in the graph changing depending on where each previous element is offset). Is there another excel function that might be used to do this?

Here's an example of the starting points of each step (24 total, with the possibility of some overlapping):
Cells 2.png

I'm open to VBA, pivot tables, whatever, just trying to find another way other than brute forcing it with a lot of unnecessary cells. Being able to transfer it into another application that can't handle that many variables (or at least i don't have the time to enter them one by one) is my main reason for this, but i'd also like to know if there is a simpler way, something more elegant. I do enjoy a pretty spreadsheet...

Trying to remember a bit of my undergrad physics (although this is getting off topic for an Excel forum!):

You say you know the starting kinetic energy K
So K = Mu^2
where:
M is the mass of the sliding object
u is its initial velocity.

The retarding force F due to dynamic friction does does not vary with velocity (agree??). So: F = Ma
where:
a is the acceleration due to the frictional force F.

As F is a constant so is "a" and so the following applies:
v^2 = u^2 + 2*a*s
where:
v is the current velocity
s is the distance traveled

When the object has come to rest v = 0 so:
0 = u^2 + 2*a*s
s = -u^2/(2*a)

Or, as KE is apparently known:

s = total distance traveled = -K/(2*a*M)

So your problem of computing distance traveled reduces to this simple equation.

I presume I must be missing something!

I'm glad someone remembers something at least Friction force is either static (the force required to get something moving) or dynamic (the force required to keep it moving).

My problem lies in that the dynamic friction force is not constant (due to increase in temperature of the surfaces). It changes after a given distance. If i had this value given as a linear function of the distance, I could solve for X. But because I only know the values as Y1 between X1 and X2, Y2 between X2 and X3 etc., i can't treat it as a linear equation. The experimental data was obtained quite a while ago (decades) and the steps in friction are easy to solve for by hand or in an individual case (i.e. a single friction element or multiple elements with set distances). There is probably a non-linear equation that can be determined from the data, but i don't have access to it. I would also be moving away from the standard method of calculation (this is for a product with a standard test certificate and a standard method of calculating its safe use).

As an engineer i'm always trying to find the laziest way of doing something, saving myself from working these calculations out by hand (might have to change the arrangement of elements many times to determine the best solution for a location). In this case it would be calculating the offsets and energy every 0.01m over 100m and then using an if statement to determine when each element begins and changes energy. I can then sum up the values at each 0.01m increment and locate the corresponding distance that matches the energy level of the moving object. This would give me the resolution i am after (and even let me do a couple of velocity/acceleration graphs that would be interesting if not useful) but isn't very compact.

What i am now realising is that i didn't mention that i am trying to put this all into a PowerApp that can be run on a phone or tablet. PowerApps can use excel tables to import data, but it can't use equations in those tables. I can enter all those cells in as text items, but i have to do it one by one. If i can reduce the number of cells down to 3 or 4 x 24, this is then in the realm of doable. Most of the equations in excel will work in PowerApps, but it's much easier to test out in excel. Once in PowerApps i can use it anywhere on my phone.

Given that logically (in my head at least) it can be solved in a few lines (on paper with a small sketch it's usually just one or two equations) i guess i was hoping that someone might think of a way of using IF statements to sort them out without the number of statements and equations required exceeding any maximum character limits (or my ability to debug).

Given that i can get it working in just a spreadsheet, it's probably an excercise in overengineering, but as usual, if something is worth doing it is worth overdoing.

Perhaps if you upload your actual workbook it will be easier to see what you are trying to do and someone will jump in with an idea or two.

To attach a file, first make sure to remove any sensitive/proprietary data and then click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.