Find a value between two range of angles in degrees

[rajaniesh]

Hi,

B	C	D	E	F	G	H
Natal House	Start House(NZ)	Degree Start	End House(NZ)	Degree End	Degree Cum Start	Degree Cum End
1	10	01°42'56.00	11	10°08'45.00	271°42'56.00	310°08'45.00
2	11	10°08'45.00	12	17°28'53.00	310°08'45.00	347°28'53.00
3	12	17°28'53.00	1	18°15'04.00	347°28'53.00	18°15'04.00
4	1	18°15'04.00	2	13°31'10.00	18°15'04.00	43°31'10.00
5	2	13°31'10.00	3	06°41'22.00	43°31'10.00	66°41'22.00
6	3	06°41'22.00	4	01°42'56.00	66°41'22.00	91°42'56.00
7	4	01°42'56.00	5	10°08'45.00	91°42'56.00	130°08'45.00
8	5	10°08'45.00	6	17°28'53.00	130°08'45.00	167°28'53.00
9	6	17°28'53.00	7	18°15'04.00	167°28'53.00	198°15'04.00
10	7	18°15'04.00	8	13°31'10.00	198°15'04.00	223°31'10.00
11	8	13°31'10.00	9	06°41'22.00	223°31'10.00	246°41'22.00
12	9	06°41'22.00	10	01°42'56.00	246°41'22.00	271°42'56.00

<tbody>
</tbody>

I want to create a formula to find a value between a range. for example 9 degree lies in this row

347°28'53.00

18°15'04.00

<tbody>
</tbody>

this is because after 347 it goes to 360 and then starts the counting till 18 degree and 9 degree lies in that. So I want to display the result of natal column in this case it is 3.

I have used this formula :=IF(AND($AA11 >=$G$8,$AA11 <$H$8),$B$8,IF(AND($AA11 >=$G$9,$AA11 <$H$9),$B$9,IF(AND($AA11 >=$G$10,$AA11 <$H$10),$B$10,IF(AND($AA11 >=$G$11,$AA11 <$H$11),$B$11,IF(AND($AA11 >=$G$12,$AA11 <$H$12),$B$12,IF(AND($AA11 >=$G$13,$AA11 <$H$13),$B$13,IF(AND($AA11 >=$G$14,$AA11 <$H$14),$B$14,IF(AND($AA11 >=$G$15,$AA11 <$H$15),$B$15,IF(AND($AA11 >=$G$16,$AA11 <$H$16),$B$16,IF(AND($AA11 >=$G$17,$AA11 <$H$17),$B$17,IF(AND($AA11 >=$G$18,$AA11 <$H$18),$B$18,IF(AND($AA11 >=$G$19,$AA11 <$H$19),$B$19,"Combination Not available"))))))))))))


but it is not working. I wanted to use switch formula but for some reason this does not show up in the excel 2016 so I have to use this kind of if formula. Anyway this is a second question.

Regards
Rajaniesh

 

[rajaniesh]

Hi MrExcel Team,

Can you please help here

Regards
Rajaniesh

 

[mikerickson]

It sounds like you are looking for one of two things.
Either you are looking for a formula that will return the angle between one vector and another when they are on different sides of the x-axis.

e.g. the angle between 315° and 45° is 90°, but the angle between 45° and 315° is 270°.
in which case a formula like =MOD(A1-B1+360, 360) would work

OR you are looking for the interior angle of two vectors, where the angle between 45° and 315° is the same as the angle between 315° and 45°, i.e. 90°

like =MIN(MOD(A2-B2+360, 360), MOD(B2-A2+360, 360))

 

[thisoldman]

The numbers look like celestial coordinates for a horoscope, to me.

I would bet the coordinates are entered as text, not as a true number with custom formatting. To do number comparisons, we will convert 9°00′00″ to decimal degrees. Note that this formula requires degrees up to 3 digits, 2-digit minutes, and seconds with 2 whole digits and 2 decimal digits. You cannot omit digits in the minutes and seconds fields.

Convert $AA11 (contains 9°00′00.00″ as text) to decimal degrees:
=LEFT($AA11,LEN($AA11)-9)+MID($AA11,LEN($AA11)-7,2)/60+RIGHT($AA11,5)/3600

I suggest using a lookup table to find the natal house. I've placed the table in P10:Q23.

	P	Q
10	Lookup	House
11	0	3
12	18.25111	4
13	43.51944	5
14	66.68944	6
15	91.71556	7
16	130.1458	8
17	167.4814	9
18	198.2511	10
19	223.5194	11
20	246.6894	12
21	271.7156	1
22	310.1458	2
23	347.4814	3

<tbody>
</tbody>

The lookup formula I suggest is:
=VLOOKUP(Converted_AA11, P11:Q23, 2,TRUE)

If you want to do the conversion and lookup in one formula, use:
=VLOOKUP(LEFT($AA11,LEN($AA11)-9)+MID($AA11,LEN($AA11)-7,2)/60+RIGHT($AA11,5)/3600, P11:Q23, 2,TRUE)

 

[rajaniesh]

Sir,

Thanks for your reply. I am looking for a formula which when applied to the I row can find out that the angle lies in which row. so for example 9 degree lies between 347°28'53.00 and 18°15'04.00(3rd Row) so it should produce the column value for Natal House which is 3 and 212 lies in 10th row because it lies between 198 and 223. I want this generic formula so it works for both the conditions.

Please let me know if I am not clear?

Regards
Rajaniesh

 

[rajaniesh]

Thank you for your reply.

You are absolutely right it is used in celestial coordinates for a horoscope. I am using this custom formatting 00"°"00"'"00.00. Let me try If this formula works for all conditions?

Regards
rajaniesh

 

[rajaniesh]

Hi,

I applied this formula LEFT($AA11,LEN($AA11)-9)+MID($AA11,LEN($AA11)-7,2)/60+RIGHT($AA11,5)/3600 on this number 09°20'00.00 but it returns invalid value error the text formatting I am using is this format 00"°"00"'"00.00

Regard
Rajaniesh

<tbody>
</tbody>

 

[mikerickson]

This should turn a DMS string into decimal degrees.
=24*SUBSTITUTE((SUBSTITUTE(SUBSTITUTE(A1,"°",":"),"'",":")),"-","")*IF(LEFT(A1,1)="-",-1,1)

 

[thisoldman]

I think I now understand. The number in A11 under General formatting would be: 90000.00
With the custom number formatting, A11 will display as 09°00'00.00

Try this.
The lookup table should be:

	P	Q
10	Lookup	House
11	0	3
12	181504	4
13	433110	5
14	664122	6
15	914256	7
16	1300845	8
17	1672853	9
18	1981504	10
19	2233110	11
20	2464122	12
21	2714256	1
22	3100845	2
23	3472853	3

<tbody>
</tbody>

The lookup formula:
=VLOOKUP($AA11, $P$11:$Q$23, 2, TRUE)

You can apply your custom number format to the cells in column P and it won't affect the results. I have shown the numbers as they display under General format.

 

[rajaniesh]

This formula does not help because after converting it into decimal when I tried to apply my formula :

IF(AND($AA11 >=$G$8,$AA11 <$H$8),$B$8,IF(AND($AA11 >=$G$9,$AA11 <$H$9),$B$9,IF(AND($AA11 >=$G$10,$AA11 <$H$10),$B$10,IF(AND($AA11 >=$G$11,$AA11 <$H$11),$B$11,IF(AND($AA11 >=$G$12,$AA11 <$H$12),$B$12,IF(AND($AA11 >=$G$13,$AA11 <$H$13),$B$13,IF(AND($AA11 >=$G$14,$AA11 <$H$14),$B$14,IF(AND($AA11 >=$G$15,$AA11 <$H$15),$B$15,IF(AND($AA11 >=$G$16,$AA11 <$H$16),$B$16,IF(AND($AA11 >=$G$17,$AA11 <$H$17),$B$17,IF(AND($AA11 >=$G$18,$AA11 <$H$18),$B$18,IF(AND($AA11 >=$G$19,$AA11 <$H$19),$B$19,"Combination Not available"))))))))))))

It does not show that 09°20'00.00 lies in the third row. I also tried it with Vlookup the results were completely wrong. Here is the quick snapshot of the excel

Degree Start (Decimal)	Degree End (Decimal)	Look Up value	Lookup value Decimal	Result(With IF formula)	With Vlookup
65142144	74420280
74420280	83348472
83348472	4356096
4356096	10394640
10394640	15938928	222°53'20.00	53407680	10	-53594640
15938928	21942144				#N/A
21942144	31220280				#N/A
31220280	40148472	294°06'40.00	70575360	1	-65142144
40148472	47556096	319°13'20.00	76591680	2	-65142144
47556096	53594640	09°20'00.00	2208000		#N/A
53594640	59138928				#N/A
59138928	65142144				#N/A

<tbody>
</tbody>