referencing distance to and from one specific point

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
147
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
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
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
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
Awesome, thanks Petter_SSs! Works great and even helped me find a mislabeled payzone cell.
 
Upvote 0
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,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top