If number between then show...

Hello,

You could probably use "IF" statements all nested together here,

first you'd need to seperate each zone for sure. I mean, if the distance is 3km, then the formula wouldn't know which zone to pick between 0-3 and 3-6. You can fix this by saying that zone 2 is 3.01 to 6 (or zone 1 is 0 to 1.999).

Then you make the formula:

Please Login or Register  to view this content.

I put my KM value in cell D1, then wrote the lower limit in column A and the upper limit in column B. The IF only looks at the upper limit and because I work from lowest to highest in the formula, the result is the one that comes last (IE the highest possible zone). When you have done the 7 zones, you would put ",Zone8" as the last "[value_if_False]" because the number should be higher than the upper limit of zone 7. This isn't perfect - a value of 1000 would result in a "zone8" result, as would the word "Hello"
Please let me know if this works for you.

Oh, and if you want to refer to a cell where the zone is mentioned, just change the formula to state the cell instead of "Zone1" etc.

What are the ACTUAL values of the different zones in kilometres?
Zone 1 is 0-3
Zone 2 is 3-6

"and so on" isnt much help
"and so on" implies they are set every 3 kilometres but that may not be the case.

What are they EXACTLY?

Originally Posted by Challebjoern

So if the total distance is 8km I want it to show "Zone 3"

Will the distances ALWAYS be whole numbers?

Is a distance of 3 km exactly zone 1 or zone 2?

If each zone is 3 km in length I'd do it this way.

=int(A1/3) +1
to get the zone number
Where A1 is your distance

Originally Posted by Challebjoern

They are like this

Zone 1 - 0-3km
Zone 2 - 3-6km
Zone 3 - 6-10km
Zone 4 - 10-14km
Zone 5 - 14-18km
Zone 6 - 18-22km
Zone 7 - 22-26km
Zone 8 - 26-30km

If the distance is 3km which zone is it? Zone 1 or Zone 2?

See how all of your distances overlap?

Ok just saw your post #5.

Why not use a vlookup?

One way...

Create the table as shown in D1:E8...

Data Range

	A	B	C	D	E
1	Distance	Zone		Zone 1	0
2	3.1	Zone 2		Zone 2	3.1
3				Zone 3	6.1
4				Zone 4	10.1
5				Zone 5	14.1
6				Zone 6	18.1
7				Zone 7	22.1
8				Zone 8	26.1
9	------	------	------	------	------

This formula entered in B2:

=INDEX(D1:D8,MATCH(A2,E1:E8))

You're welcome. We appreciate the feedback!