Converting Addresses to Parking Spaces

Hi all,

I'm stuck.

I'm trying to find a way to get the overlapping spaces based on the addresses in permits.

for example, 2252-2204 = 48, but since they're only counting one side of the street, it's 24 addresses. The spaces on the block don't correlate to the amount of addresses in any way, except for the fact that we receive the permits based on addresses and we have to figure out the overlapping spaces ourselves. So, this block has 24 addresses and (according to our database), 25 spaces.


The permit goes from 2250 to 2300, but since we're only counting the spaces on this particular block, the address caps at 2252 (So (2252-2250)/2 - 1, so conceivably, it's just the last space, but it keeps giving me space #24.)

The permits say that the last two addresses will be blocked off, so probably space 25, but I have several different formulas and VBA codes that JUST DON'T work for whatever reason. I've been working on this for two days, and it's probably stupidly simple, but I just don't have the brain power anymore.

Block Start	Block End	Spaces on Block	Permit Start	Permit End
2204	2252	25	2250	2300
2907	2963	23	2933	2943
3402	3454	24	3423	3426
3405	3425	9	3423	3426
2200	2226	10	2220	2236
5	25	9	15	25

Can't offer you a formula solution but it may help to adjust your rationale...

Assuming these are like street addresses where even numbers are one side of street and odd are other side of street, 2204 to 2252 is assumed to be the even side where it would be 2252-(2204-1)=49 addresses inclusive. The number of even addresses would be (49+1)/2 or 49/2+0.5=25. However, using this premise don't jive with other listings. For example, 2963-(2907-1)=57 and (57+1)/2=29, yet your database only lists 23 spaces on the block... so there has to be address numbers which have no correlating space. There is no way to determine from existing data which addresses have no correlating space.

In order to determine what you desire, you will likely require a complete listing of space addresses.

Yeah, the spaces and addresses have no correlation. As an example, think of a block with 10 spaces. Let's say that block has one big skyscraper, and the addresses are 1600-1602. We know that every space is 1/10th of an address. Now take another block with 10 parking spaces and let's say it's apartment buildings from 1400-1440. We know that there are two addresses for every space (It's exaggerated, but that's how it works)

So, here's the formula we've been working with, written by the Data Manager before me. Until yesterday, we've been using it, but we found a problem in the data from the first row in the table above. The permit goes past the end of the block, but it's giving us the second to last space

A1: 2204 B1: 2252 C1: 25 D1: 2250 E1: 2300

Formula:

Please Login or Register  to view this content.

Thanks,
Drew

Perfect, thank you.