# referencing distance to and from one specific point

#### y3tter

##### Board Regular
In column A I have a unique location, column B are all present zones in each unique location, column C is the thickness of each zone, column D is distance to/from payzone top from the top of each zone. The last zone in each location does not have a defined thickness and isn't necessary. Payzone always has a thickness, but will be defined as 0 in column D to reference the top of the payzone. Is there a formula that I could use in column D to populate the distances for each unique location, like I have defined below? Perhaps identifying each location then looking for first negative value, defining it as 0 and then summing values until it reaches the next location? Any help would be greatly appreciated.

 Location A Zone 1 32 156 Location A Zone 2 116 124 Location A Zone 3 8 8 Location A Payzone -9 0 Location A Zone 4 -8 -9 Location A Zone 5 -17 Location B Zone 1 9 9 Location B Payzone -10 0 Location B Zone 2 -2 -12 Location B Zone 3 -3 -15

<tbody>
</tbody>

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Peter_SSs

##### MrExcel MVP, Moderator
I don't understand the expected results, refer to the coloured cells below.

Why is D6=C5 but correspondingly D10<>C9?

Excel Workbook
ABCD
1
2Location AZone 132156
3Location AZone 2116124
4Location AZone 388
5Location APayzone-90
6Location AZone 4-8-9
7Location AZone 5-17
8Location BZone 199
9Location BPayzone-100
10Location BZone 2-2-12
11Location BZone 3-3-15
Distance

#### y3tter

##### Board Regular
Location B has an error. For location B in column D it should be: 9, 0, -10, -12. There also shouldn't have been a value in cell C11.

#### Peter_SSs

##### MrExcel MVP, Moderator
Try this, copied down.

Excel Workbook
ABCD
1
2Location AZone 132156
3Location AZone 2116124
4Location AZone 388
5Location APayzone-90
6Location AZone 4-8-9
7Location AZone 5-17
8Location BZone 199
9Location BPayzone-100
10Location BZone 2-2-10
11Location BZone 3-12
Distance

#### Peter_SSs

##### MrExcel MVP, Moderator
Hmm, this considerably shorter one may do the job also.

Excel Workbook
ABCD
1
2Location AZone 132156
3Location AZone 2116124
4Location AZone 388
5Location APayzone-90
6Location AZone 4-8-9
7Location AZone 5-17
8Location BZone 199
9Location BPayzone-100
10Location BZone 2-2-10
11Location BZone 3-12
Distance

Last edited:

#### y3tter

##### Board Regular
Awesome, thanks Petter_SSs! Works great and even helped me find a mislabeled payzone cell.

#### Peter_SSs

##### MrExcel MVP, Moderator
Awesome, thanks Petter_SSs! Works great and even helped me find a mislabeled payzone cell.
Good news!
Thanks for the feedback.

#### Peter_SSs

##### MrExcel MVP, Moderator
Thinking further about this: If every Location starts with a positive number in column C and ends with a blank in column C then you could try this even more compact one.

Excel Workbook
ABCD
1H1H2H3H4
2Location AZone 132156
3Location AZone 2116124
4Location AZone 388
5Location APayzone-90
6Location AZone 4-8-9
7Location AZone 5-17
8Location BZone 199
9Location BPayzone-100
10Location BZone 2-2-10
11Location BZone 3-12
Distance

Last edited:

works great!

Replies
4
Views
798
Replies
4
Views
728
Replies
15
Views
742
Replies
2
Views
387
Replies
6
Views
1K

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back