Tom M
asked on
Percent of line use calculation
Take a look at this spreadsheet what I would like for a solution would be column F calculating this dynamically as I add new information to column a-e. so if you look at examples 1 & 2 you will see what the percentage should have been in columns a-f would have as of the dates in history that are in the examples.
Initially customer 11 was on line 1 2381ft he was at 100 percent of line use on 3/1/2010. then on 1/1/2012 customer 13 (example 1) came on to line 1 at the 500 foot mark, the two then share 500ft and customer 11 was responsible for 100 percent of the remaining line. Then on 10/1/2016 customer 14 joined the line at the 300 foot mark so customers 11,13 & 14 shared 300 feet customers 11&13 shared an additional 200 feet and customer one was solely responsible for the remaining footage. So again i would like this line percentage calculated in column f as i add more and more customers to the various lines
book1.xlsx
Initially customer 11 was on line 1 2381ft he was at 100 percent of line use on 3/1/2010. then on 1/1/2012 customer 13 (example 1) came on to line 1 at the 500 foot mark, the two then share 500ft and customer 11 was responsible for 100 percent of the remaining line. Then on 10/1/2016 customer 14 joined the line at the 300 foot mark so customers 11,13 & 14 shared 300 feet customers 11&13 shared an additional 200 feet and customer one was solely responsible for the remaining footage. So again i would like this line percentage calculated in column f as i add more and more customers to the various lines
book1.xlsx
ASKER
No I am sorry they aren't the results I am looking for. The correct results are displayed in example 2, your solution is adding additional customers to the length of the line. I should mention I am not adverse to changing the layout or adding additional columns if need be I just need to get one column that calculates the results accurately
btw, can draw and post a sketch of it?
ASKER
ste5an
Your formula isn't doing what I am asking, it's no different than what you gave me the other day. In your example 2 you are referencing the yellow column that I manually calculated for display purposes, of course that is going to be right :). If your solution worked your screen shot of column f would match the yellow column in example 2. Like I explained the other day your formula adds to the length of the line rather than recognizing it as fixed.
Your formula isn't doing what I am asking, it's no different than what you gave me the other day. In your example 2 you are referencing the yellow column that I manually calculated for display purposes, of course that is going to be right :). If your solution worked your screen shot of column f would match the yellow column in example 2. Like I explained the other day your formula adds to the length of the line rather than recognizing it as fixed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the points! :) glad I could help
Open in new window
Are these the results you are looking for?Paul