referencing distance to and from one specific point

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
142
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 AZone 132156
Location AZone 2116124
Location AZone 388
Location APayzone-90
Location AZone 4-8-9
Location AZone 5-17
Location BZone 199
Location BPayzone-100
Location BZone 2-2-12
Location BZone 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
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
142
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.
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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:
Upvote 0

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
142
Awesome, thanks Petter_SSs! Works great and even helped me find a mislabeled payzone cell.
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
Awesome, thanks Petter_SSs! Works great and even helped me find a mislabeled payzone cell.
Good news! :)
Thanks for the feedback.
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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:
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top