Adding rates from Overlapping Data

[Asdesche]

I am attempting to create an excel formula sheet that will allow me to add the rates of a construction material dumped at a location to a specific alignment when some of the locations overlap.

For Example, using stations, one location might be
100+00 to 110+00 with a rate of 110.27

another location would be

101+00 to 111+00 with a rate of 89.73

From station 100+00 to 101+00 the rate would be 110.27. Then from 101+00 to 110+00, the rate would be 200. Finally from 110+00 to 110+00 it would be 89.63.

Lets say the alignment was from 99+00 to 112+00.

My objective, as I said earlier, is to represent the entire alignment with the total rate for each station accounting for each area of overlap. And allow me to add new locations in the future that are distributed to their correct stations.

The next issue after that for partial stations where the last two digits are nonzero. It would be incredibly annoying to have a list of every station like 99+00,99+01,99+02, etc,. So I'm attempting to find an equation that would add the partial stations into the closest station after converting the rate into a more appropriate number. Meaning if the station was 110+49, and the rate was 100, I want 49 tons to be added to 109+00 to 110+00.

I've been having some serious issues getting something to work, mostly because I'm not very skilled at excel but I know there is a simple solution out there I don't fully know how to do.

Any help would be appreciated!

P.S. Stations for those that may not know are a representation of feet or meters in a simple form from a reference point. So 100+00 is 10000 ft from a reference point.

[bebo021999]

Meaning if the station was 110+49, and the rate was 100, I want 49 tons to be added to 109+00 to 110+00.

May I interprete 110+49 = 11049 tons?

[Asdesche]

May I interprete 110+49 = 11049 tons?

Sorry my mistake. No stations represent a location the rate is
The tonnage. So imagine 110+00 as a specific point. Like 11000 is just a point on a map. You could even use x,y or z. I'm just using stations because that will end up being what I use in the end

[Asdesche]

The rate is a tons per station. So if the station is from 100+00 to 101+00 it would be one station. If the rate is 100 tons per station there is 100 tons of material from 100+00 to 101+00

[bebo021999]

Is it are you looking for?

I have no idea which format layout your project would to be, but I try follow my understanding.

[Asdesche]

Sorry for the misunderstanding. I probably should have explained better.
So let's say we had twenty locations where material was dumped
Some generic stations.
100+00 to 103+00, rate 200
101+00 to 104+00, rate 200
102+00 to 105+00, rate 200
Etc.,
I want to create an Excel sheet that will represent the alignment from start to finish and add every rate that corresponds to a specific amount of the alignment. So if the alignment is from 100+00 to 123+00, I want the calculation to find every rate that needs to be added to the alignment from the location Information. At station 100+00 to 101+00 the rate is 200, then it increase to 400 and then it climbs to 600 and remains that way until station 120+00.

I want to be able to put in a new location let's say
From 100+00 to 110+00, rate 100, and it distribute to the alignment. The problem is there are partial stations, in between, and I don't want to have to respresent all those in between stations.

[bebo021999]

Could you show us how your layout would be?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Asdesche]

Here is the sample excel sheet. When you take a look at it, you'll notice some locations have partial stations, and so the other issue I have is I want to have the rate from that partial station distribute into the closest stations. So
the first location is 280+20, so the rate would fall 20% into 279+00 to 280+00, and 80% into 280+00 to 281+00. Though if that's not possible or too difficult, the first issue is the only one I need to solve.

[bebo021999]

Not fully understanding but below formula do exactly as your expected result:

In M2 then copy down:

Please Login or Register  to view this content.

[FlameRetired]

There are also rounding errors. M11:M16 show 74.31. They should be 74.30.

Using Quang's formula

Please Login or Register  to view this content.