Spread the value of a cell into multiple cells, with lessening amounts.

[JakkeJakobsen]

Okay, this will need some preface. A lot actually. Bowling lanes are dressed with oil. For my example, there is one moving nozzle that squirts oil on a static brush. This is then transferred to a moving, round brush by being in contact with the static brush. The round brush, called a buffer brush, is what puts the oil down the lane. Now, my oil pump is set to 50ul, which means that each board on the bowling lane (there are 39, 37 are oiled by the machine) gets 50ul each time the oil head moves over that area. Now, that means that for every 1/4 foot, there is 9.375ul of oil per board for the first 4 feet (16 cells). That is the total amount of oil present on that board. When there is not squirted oil, the buffer brush still spins, and "buffs" out the oil it has put down, as well as the remaining oil in the brush (it never goes dry).

Currently, I just have a formula like this: =if($AR3="","",if(D3<>AU2,D3+(AU2-D3)/1.02,D3))

What this does, is basically to check if there is any change, like going from 9.375 to 0, and if so, it will divide the previous part by 1.02 and add it to whatever is in the current cell, which in this example is 0. This goes on and on till the end of the oiled area, called an oil pattern.

The issue with this method is that it ADDS oil where there is none, as (9.375*16)+(9.375/1.03) is not 50, its 50+9.19.

What I want to do, is to take the total oil amount for one board, and smear it out like it does in real life. If the total amount is 50ul on that board, the total amount is also 50, but dragged down the lane.

Here is a picture of how it looks currently. This isn't that far off reality, but when you start adding stuff instead of just dragging it out, things get a bit off. I can say that in the center at 0 feet, there is 100, this is a number I multiplied into getting, related to the 9.375, but multiplied up to always be 100. On the outmost board with oil there is 80. Now, if you look in the picture I provided, in the Composite Graph, you can see there is 0.2ml of oil in total on the outmost board, while the center board has 0.95ml. 0.95/0.2 is not the same as 100/80. There is my issue in simple words.

Happy for any help!

 

[JakkeJakobsen]

Here you can see how the oil is put out, or rather where it is put out. Now, as you can see, if you add each of these sections, they all equal 50.

 

[JakkeJakobsen]

To say it very simple: If the total amount of oil on a board is 200ul, so 0.2ml, and the pattern is 40 feet, which is 160 cells, there will be oil all the way down, even if there is just 0.00001ml of oil at the end, there is some.
So if it was only 1 feet long with 200ul, it could be 103.125-53.125-28.125-15.625. How long it buffs, that part I can add on later, it depends on the machine speed too, which is also why the amount of oil is spread out differently in the picture above (9.375 and 6.25). In a different revision of the sheet, the formula which I posted in the first post is instead as following, using column A, which has the speed in inches per second:

=if($A5="","",(D4+(AU3-D4)/(1+($A4/1100))))

This does not check if there are changes to the pattern or not.

 

[JakkeJakobsen]

This illustrates more accurately what I want to happen. Both the top and bottom is the same total amount.