I Need a math guru
 

I am trying to develop a formula so that Excel can calculate the range for a
wargame. The game is called Battletech if you know what it is. It is
played on a map that has a hex grid measuring ~3/4" a side. The hexes are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered 1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the position
of each unit on the map. and calculate 2 legs of a triangle, but it is not
always a right triangle (I struggled with it for 3 days before I realized wy
a^2+b^2=c^2 wasn't working.... It has been almost 20 years since I took a
geometry class.....

Thanks in advance for any help I can get.

Adam

I Need a math guru
 

Hi Adam,

I don't understand what you have and what you're trying to calculate.
If you have two legs, you have three points, haven't you? So you can
calculate the third leg the same way.
Please explain a bit more and give an example

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
I am trying to develop a formula so that Excel can calculate the range for
a wargame. The game is called Battletech if you know what it is. It is
played on a map that has a hex grid measuring ~3/4" a side. The hexes are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered 1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the
position of each unit on the map. and calculate 2 legs of a triangle, but
it is not always a right triangle (I struggled with it for 3 days before I
realized wy a^2+b^2=c^2 wasn't working.... It has been almost 20 years
since I took a geometry class.....

Thanks in advance for any help I can get.

Adam

I Need a math guru
 

The problem, I think, comes from the fact that the map is built on a
hexoganal "grid". The map numbering looks like this

0111 0311
0211 0411
0112 0312
0212 0412
0113 0313
0213 0413
0114 0314
0214 0414

So if a unit is in hex 0114, and another is in 0311, the "hand counted"
distance is 4, but using =SQRT(ABS(A1-A2)^2+ABS(B1-B2)^2) to calculate it
results in 3.61 wich works. However, if I am using map locations 1516 &
0201, the counted distance is 21 but the formula returns 19.85 with does not
work.

The Hexes on the map are 1 5/16" accross (flat to flat) (3/4" on a side)so
if units are 2 hexes away (verticle) their physical distance is just over
2.5" BUT if they are 2 hexes away horizontally the physical distance is only
~2.25" at close ranges, the difference is negligable, but as the range
increases.....

I can almost see the answer, but it is escaping me.

I hope this helps.
Adam


"Niek Otten" wrote in message
...
Hi Adam,

I don't understand what you have and what you're trying to calculate.
If you have two legs, you have three points, haven't you? So you can
calculate the third leg the same way.
Please explain a bit more and give an example

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
I am trying to develop a formula so that Excel can calculate the range for
a wargame. The game is called Battletech if you know what it is. It is
played on a map that has a hex grid measuring ~3/4" a side. The hexes are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered 1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the
position of each unit on the map. and calculate 2 legs of a triangle, but
it is not always a right triangle (I struggled with it for 3 days before I
realized wy a^2+b^2=c^2 wasn't working.... It has been almost 20 years
since I took a geometry class.....

Thanks in advance for any help I can get.

Adam

I Need a math guru
 

I'll take a stab... looks like the question isn't one of geometry, but
rather of layout. Let's have a1,b1 be the location of the starting point
(15, 16 in your second example) and a2,b2 the location of the ending point
(2,1). It looks to me as if you have to consider which dimension has the
furthest to move. In making that move, you have the OPTION to move up to as
far in the other dimension. So in c1, calculate the number of steps in the
major dimension as =max(abs(a1-a2),abs(b1-b2). In c2, the steps in the minor
dimension is =min(abs(a1-a2),abs(b1-b2)). The total steps in c3 is
=c1+max(0,c2-int(c1/2)). It seems to work for the examples you offered, but
I'd test a little further... If it's not quite right, please provide some
counterexamples and the correct results.
HTH. --Bruce

"Adam Kroger @hotmail.com" wrote:

The problem, I think, comes from the fact that the map is built on a
hexoganal "grid". The map numbering looks like this

0111 0311
0211 0411
0112 0312
0212 0412
0113 0313
0213 0413
0114 0314
0214 0414

So if a unit is in hex 0114, and another is in 0311, the "hand counted"
distance is 4, but using =SQRT(ABS(A1-A2)^2+ABS(B1-B2)^2) to calculate it
results in 3.61 wich works. However, if I am using map locations 1516 &
0201, the counted distance is 21 but the formula returns 19.85 with does not
work.

The Hexes on the map are 1 5/16" accross (flat to flat) (3/4" on a side)so
if units are 2 hexes away (verticle) their physical distance is just over
2.5" BUT if they are 2 hexes away horizontally the physical distance is only
~2.25" at close ranges, the difference is negligable, but as the range
increases.....

I can almost see the answer, but it is escaping me.

I hope this helps.
Adam


"Niek Otten" wrote in message
...
Hi Adam,

I don't understand what you have and what you're trying to calculate.
If you have two legs, you have three points, haven't you? So you can
calculate the third leg the same way.
Please explain a bit more and give an example

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
I am trying to develop a formula so that Excel can calculate the range for
a wargame. The game is called Battletech if you know what it is. It is
played on a map that has a hex grid measuring ~3/4" a side. The hexes are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered 1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the
position of each unit on the map. and calculate 2 legs of a triangle, but
it is not always a right triangle (I struggled with it for 3 days before I
realized wy a^2+b^2=c^2 wasn't working.... It has been almost 20 years
since I took a geometry class.....

Thanks in advance for any help I can get.

Adam

I Need a math guru
 

It works some of the time. I have done further work on using the
Pathagareum (sp?) using half measurements so that it always comes as a right
angle. I might be breaking a rule here, if I am I apologize, but I am
pasting the side-by-side results of both methods below as a table. The
formatting isn't perfect, I hope you aren't using a text only email reader
or this will probably be garbage..... I did testing and discovered that
there was a pattern I could express as a function to get the rise and run
counted by half hexes reliably. I do not understand what the MAX() & MIN()
do. I came up with the multiplier by looking at teh hypotonuse of the
triange my formulas vreated and the range that was counted by hand. I
notice, that when our formulas do not work, the answer lies between them....






1

2




H
V










1
14
A
2
Run
3

3

formula 1


3
11
B
3
Rise
6

2

1.64
Hyp mod

Range
4


4.00

4.00

RISE FORMULA










run odd
2(x-1)+1










run even
2x

7
11
A
6
Run
9

6

RUN FORMULA

13
13
B
2
Rise
4

2

all
0+1.5x

Range
6


6.00

6.00

Range formula
ROUND(SQRT(run^2+rise^2)/Hyp mod,0)

























15
16
A
13
Run
19.5

15

formula 2


2
1
B
15
Rise
29

13

MAX(run,rise))

Range
21


21.00

21.00

MIN(run,rise)










max+MAX(0,mivn-INT(max/2))













15
16
A
1
Run
1.5

1




14
15
B
1
Rise
1

1




Range
1


1.00

2.00




























5
15
A
2
Run
3

2




7
14
B
1
Rise
2

1




Range
2


2.00

2.00




























3
16
A
2
Run
3

10




5
6
B
10
Rise
20

2




Range
11


12.00

10.00




























3
16
A
9
Run
13.5

9




12
9
B
7
Rise
13

7




Range
11


11.00

12.00




























1
17
A
1
Run
1.5

16




2
1
B
16
Rise
31

1




Range
16


19.00

16.00




























1
17
A
6
Run
9

16




7
1
B
16
Rise
32

6




Range
19


20.00

16.00
















1
17
A
5
Run
7.5

16




6
1
B
16
Rise
31

5




Range
18


19.00

16.00





"bpeltzer" wrote in message
...
I'll take a stab... looks like the question isn't one of geometry, but
rather of layout. Let's have a1,b1 be the location of the starting point
(15, 16 in your second example) and a2,b2 the location of the ending point
(2,1). It looks to me as if you have to consider which dimension has the
furthest to move. In making that move, you have the OPTION to move up to
as
far in the other dimension. So in c1, calculate the number of steps in
the
major dimension as =max(abs(a1-a2),abs(b1-b2). In c2, the steps in the
minor
dimension is =min(abs(a1-a2),abs(b1-b2)). The total steps in c3 is
=c1+max(0,c2-int(c1/2)). It seems to work for the examples you offered,
but
I'd test a little further... If it's not quite right, please provide some
counterexamples and the correct results.
HTH. --Bruce

"Adam Kroger @hotmail.com" wrote:

The problem, I think, comes from the fact that the map is built on a
hexoganal "grid". The map numbering looks like this

0111 0311
0211 0411
0112 0312
0212 0412
0113 0313
0213 0413
0114 0314
0214 0414

So if a unit is in hex 0114, and another is in 0311, the "hand counted"
distance is 4, but using =SQRT(ABS(A1-A2)^2+ABS(B1-B2)^2) to calculate it
results in 3.61 wich works. However, if I am using map locations 1516 &
0201, the counted distance is 21 but the formula returns 19.85 with does
not
work.

The Hexes on the map are 1 5/16" accross (flat to flat) (3/4" on a
side)so
if units are 2 hexes away (verticle) their physical distance is just over
2.5" BUT if they are 2 hexes away horizontally the physical distance is
only
~2.25" at close ranges, the difference is negligable, but as the range
increases.....

I can almost see the answer, but it is escaping me.

I hope this helps.
Adam


"Niek Otten" wrote in message
...
Hi Adam,

I don't understand what you have and what you're trying to calculate.
If you have two legs, you have three points, haven't you? So you can
calculate the third leg the same way.
Please explain a bit more and give an example

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
I am trying to develop a formula so that Excel can calculate the range
for
a wargame. The game is called Battletech if you know what it is. It
is
played on a map that has a hex grid measuring ~3/4" a side. The hexes
are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered
1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the
position of each unit on the map. and calculate 2 legs of a triangle,
but
it is not always a right triangle (I struggled with it for 3 days
before I
realized wy a^2+b^2=c^2 wasn't working.... It has been almost 20 years
since I took a geometry class.....

Thanks in advance for any help I can get.

Adam

I Need a math guru
 

Well trying to save myself some typing did not work too well. Sorry about
the garbaged earlier post. I won't make that mistake again. Here is where
I have gotten with the formula(s):

To calculate the rise:
If run (actual) is ODD 2(x-1)+1
If run (actual) is even 2x
To calculate the run
all values 0+1.5x

reason: I noticed that the sriangulations were causing non right angles in
some instances. So instead of counting full hexes in the calculations I
used half hexes. This made them be right triangles (I hope). In the
process I made a table and saw that the number of "half-hexes" followed a
predictable pattern and was able to create an equation to pridict it at any
value. All well and good; however, I think there must be a similar pattern
for the hypotonuse of the triangle as I am still not getting the right
answer all the time, particularly at larger distances, wich tells me I have
a small error somewhere in my formulas that is getting magnified.

The problem with the Min and Max (I think) is that you still have to
compensate for the nature of a hexoganol grid layout. The hexes don't tun
Up-Down, Left-Right They actually run up-down, angle-left-right. If you
don't compensate then A^2+B^2=C^2 can't work because it is not a right
angle. Without the right angle you need too sides and an angle measurement,
and I can't think of a way to define the angle.




It works some of the time. I have done further work on using the
Pathagareum (sp?) using half measurements so that it always comes as a right
angle. I might be breaking a rule here, if I am I apologize, but I am
pasting the side-by-side results of both methods below as a table. The
formatting isn't perfect, I hope you aren't using a text only email reader
or this will probably be garbage..... I did testing and discovered that
there was a pattern I could express as a function to get the rise and run
counted by half hexes reliably. I do not understand what the MAX() & MIN()
do. I came up with the multiplier by looking at teh hypotonuse of the
triange my formulas vreated and the range that was counted by hand. I
notice, that when our formulas do not work, the answer lies between them....



"bpeltzer" wrote in message
...
I'll take a stab... looks like the question isn't one of geometry, but
rather of layout. Let's have a1,b1 be the location of the starting point
(15, 16 in your second example) and a2,b2 the location of the ending
point
(2,1). It looks to me as if you have to consider which dimension has th
furthest to move. In making that move, you have the OPTION to move up to
as
far in the other dimension. So in c1, calculate the number of steps in
the
major dimension as =max(abs(a1-a2),abs(b1-b2). In c2, the steps in the
minor
dimension is =min(abs(a1-a2),abs(b1-b2)). The total steps in c3 is
=c1+max(0,c2-int(c1/2)). It seems to work for the examples you offered,
but
I'd test a little further... If it's not quite right, please provide
some
counterexamples and the correct results.
HTH. --Bruce

"Adam Kroger @hotmail.com" wrote:

The problem, I think, comes from the fact that the map is built on a
hexoganal "grid". The map numbering looks like this

0111 0311
0211 0411
0112 0312
0212 0412
0113 0313
0213 0413
0114 0314
0214 0414

So if a unit is in hex 0114, and another is in 0311, the "hand counted"
distance is 4, but using =SQRT(ABS(A1-A2)^2+ABS(B1-B2)^2) to calculate
it
results in 3.61 wich works. However, if I am using map locations 1516 &
0201, the counted distance is 21 but the formula returns 19.85 with does
not
work.

The Hexes on the map are 1 5/16" accross (flat to flat) (3/4" on a
side)so
if units are 2 hexes away (verticle) their physical distance is just
over
2.5" BUT if they are 2 hexes away horizontally the physical distance is
only
~2.25" at close ranges, the difference is negligable, but as the range
increases.....

I can almost see the answer, but it is escaping me.

I hope this helps.
Adam


"Niek Otten" wrote in message
...
Hi Adam,

I don't understand what you have and what you're trying to calculate.
If you have two legs, you have three points, haven't you? So you can
calculate the third leg the same way.
Please explain a bit more and give an example

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
I am trying to develop a formula so that Excel can calculate the range
for
a wargame. The game is called Battletech if you know what it is. It
is
played on a map that has a hex grid measuring ~3/4" a side. The hexes
are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered
1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the
position of each unit on the map. and calculate 2 legs of a triangle,
but
it is not always a right triangle (I struggled with it for 3 days
before I
realized wy a^2+b^2=c^2 wasn't working.... It has been almost 20 years
since I took a geometry class.....

Thanks in advance for any help I can get.

Adam

I Need a math guru
 

THANK YOU

I made a slight mod to your 3rd formuls (ROUND() instead of INT()) and it
works. I still don't understand how, but it does :)

Adam

"bpeltzer" wrote in message
...
I'll take a stab... looks like the question isn't one of geometry, but
rather of layout. Let's have a1,b1 be the location of the starting point
(15, 16 in your second example) and a2,b2 the location of the ending point
(2,1). It looks to me as if you have to consider which dimension has the
furthest to move. In making that move, you have the OPTION to move up to
as
far in the other dimension. So in c1, calculate the number of steps in
the
major dimension as =max(abs(a1-a2),abs(b1-b2). In c2, the steps in the
minor
dimension is =min(abs(a1-a2),abs(b1-b2)). The total steps in c3 is
=c1+max(0,c2-int(c1/2)). It seems to work for the examples you offered,
but
I'd test a little further... If it's not quite right, please provide some
counterexamples and the correct results.
HTH. --Bruce

"Adam Kroger @hotmail.com" wrote:

The problem, I think, comes from the fact that the map is built on a
hexoganal "grid". The map numbering looks like this

0111 0311
0211 0411
0112 0312
0212 0412
0113 0313
0213 0413
0114 0314
0214 0414

So if a unit is in hex 0114, and another is in 0311, the "hand counted"
distance is 4, but using =SQRT(ABS(A1-A2)^2+ABS(B1-B2)^2) to calculate it
results in 3.61 wich works. However, if I am using map locations 1516 &
0201, the counted distance is 21 but the formula returns 19.85 with does
not
work.

The Hexes on the map are 1 5/16" accross (flat to flat) (3/4" on a
side)so
if units are 2 hexes away (verticle) their physical distance is just over
2.5" BUT if they are 2 hexes away horizontally the physical distance is
only
~2.25" at close ranges, the difference is negligable, but as the range
increases.....

I can almost see the answer, but it is escaping me.

I hope this helps.
Adam


"Niek Otten" wrote in message
...
Hi Adam,

I don't understand what you have and what you're trying to calculate.
If you have two legs, you have three points, haven't you? So you can
calculate the third leg the same way.
Please explain a bit more and give an example

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
I am trying to develop a formula so that Excel can calculate the range
for
a wargame. The game is called Battletech if you know what it is. It
is
played on a map that has a hex grid measuring ~3/4" a side. The hexes
are
numbered (4 digit 01 - 15 accross and 01 - 17 up top left is numbered
1001
bottom right 1517 The map is 15x17 ) so I am able to give Excel the
position of each unit on the map. and calculate 2 legs of a triangle,
but
it is not always a right triangle (I struggled with it for 3 days
before I
realized wy a^2+b^2=c^2 wasn't working.... It has been almost 20 years
since I took a geometry class.....

Thanks in advance for any help I can get.

Adam