asked on

Footage remaining and customer count

I am looking for a solution to calculate automatically the two columns in green. Column f represents the amount of line left that a customer is responsible for example we are working on is Line 1 which is 2381 total feel customer 11 is at the very end of the line while customer 13 is at the 500 foot mark and customer 14 is at the 300 foot mark for column F I need a formula that calculates the difference between the number a customer joins the line at and the next customer on the line. I manually put the correct numbers in. For column G I need it calculate the total number using the line at the foot mark where the that customer hooks to the line.
book2.xlsx

Tom Farrar

You can play with these formulas, but they might only work if the data is sorted by Line Number and Date.

EE1.xlsx

Tom M

ASKER

It doesn't appear to work even then. The numbers in green on my sheet are accurate so if the solution doesn't match them it's not working :(

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

Here is an alternative formula for the number of responsible Customer ID if you have an older version of Excel:

=SUMPRODUCT((B$2:B2=B2)/COUNTIFS(B$2:B2,B$2:B2,D$2:D2,D$2:D2))

Open in new window

Tom M

ASKER

There is one small issue with the Formula for responsible feet if I say customer adds to line 1 at the 900 foot mark it then gives my negative numbers for the customers on the line at the 300 and 500 foot mark. It seems to work okay so long as the customers are enter in distance declining but I am trying to make this sheet so a person can just key the data as people add to the line.

Tom M

ASKER

Maybe I could add another sheet that queries this one and put them in order on another tab??

byundt

Could you please add data to your sample sheet along with the desired results of the formula?

Tom M

ASKER

book2 (1).xlsx

I entered the correct values to the right

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom M

ASKER

Perfect thanks a bunch for your help!

Tom M

ASKER

I hate to bother you again and if it makes more sense for me to open another question please just let me know. The issue I am seeing is in the # of customer ID's responsible. That number is supposed to represent the number of customers that share the length of line displayed in the length responsible line. It appears to be just counting the total number of customers on the line as i add more folks to the sheet. Again sorry to bother you and I will post as another question if it help your rating or something..

byundt

Tom,
Please post some sample data and desired results that illustrate the problem.

Brad

Tom M

ASKER

book2.xlsx
I plugged the correct numbers into column H. It's basically the number of customers that share the length of line displayed in column F

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial