Find a value between two range of angles in degrees

[rajaniesh]

Hi,


[TABLE="class: grid, width: 518"]
<tbody>[TR]
[TD="width: 94, bgcolor: transparent"]B
[/TD]
[TD="width: 106, bgcolor: transparent"]C
[/TD]
[TD="width: 84, bgcolor: transparent"]D
[/TD]
[TD="width: 100, bgcolor: transparent"]E
[/TD]
[TD="width: 78, bgcolor: transparent"]F
[/TD]
[TD="width: 116, bgcolor: transparent"]G
[/TD]
[TD="width: 110, bgcolor: transparent"]H
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Natal House
[/TD]
[TD="bgcolor: transparent"]Start House(NZ)
[/TD]
[TD="bgcolor: transparent"]Degree Start
[/TD]
[TD="bgcolor: transparent"]End House(NZ)
[/TD]
[TD="bgcolor: transparent"]Degree End
[/TD]
[TD="bgcolor: transparent"]Degree Cum Start
[/TD]
[TD="bgcolor: transparent"]Degree Cum End
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]01°42'56.00
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]10°08'45.00
[/TD]
[TD="bgcolor: transparent, align: right"]271°42'56.00
[/TD]
[TD="bgcolor: transparent, align: right"]310°08'45.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]10°08'45.00
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]17°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]310°08'45.00
[/TD]
[TD="bgcolor: transparent, align: right"]347°28'53.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]17°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00
[/TD]
[TD="bgcolor: transparent, align: right"]347°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]13°31'10.00
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00
[/TD]
[TD="bgcolor: transparent, align: right"]43°31'10.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]13°31'10.00
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]06°41'22.00
[/TD]
[TD="bgcolor: transparent, align: right"]43°31'10.00
[/TD]
[TD="bgcolor: transparent, align: right"]66°41'22.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]06°41'22.00
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]01°42'56.00
[/TD]
[TD="bgcolor: transparent, align: right"]66°41'22.00
[/TD]
[TD="bgcolor: transparent, align: right"]91°42'56.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]01°42'56.00
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]10°08'45.00
[/TD]
[TD="bgcolor: transparent, align: right"]91°42'56.00
[/TD]
[TD="bgcolor: transparent, align: right"]130°08'45.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]10°08'45.00
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent, align: right"]17°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]130°08'45.00
[/TD]
[TD="bgcolor: transparent, align: right"]167°28'53.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent, align: right"]17°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00
[/TD]
[TD="bgcolor: transparent, align: right"]167°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]198°15'04.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]13°31'10.00
[/TD]
[TD="bgcolor: transparent, align: right"]198°15'04.00
[/TD]
[TD="bgcolor: transparent, align: right"]223°31'10.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]13°31'10.00
[/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent, align: right"]06°41'22.00
[/TD]
[TD="bgcolor: transparent, align: right"]223°31'10.00
[/TD]
[TD="bgcolor: transparent, align: right"]246°41'22.00
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent, align: right"]06°41'22.00
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]01°42'56.00
[/TD]
[TD="bgcolor: transparent, align: right"]246°41'22.00
[/TD]
[TD="bgcolor: transparent, align: right"]271°42'56.00
[/TD]
[/TR]
</tbody>[/TABLE]
I want to create a formula to find a value between a range. for example 9 degree lies in this row
[TABLE="class: grid, width: 518"]
<tbody>[TR]
[TD="bgcolor: transparent, align: right"]347°28'53.00
[/TD]
[TD="bgcolor: transparent, align: right"]18°15'04.00

[/TD]
[/TR]
</tbody>[/TABLE]
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

 

[thisoldman]

rajaniesh,

Would you go back to your original data, and please poat what you have in the cells without custom formatting. Simply change it to "General" formatting temporarily.

Also, please tell us whether =ISNUMBER(H11) returns TRUE or FALSE.

Thank you.

 

[rajaniesh]

First of All I am sorry for not replying to your request last week.

ISNUMBER(H11) returns TRUE.

Here is the text version of the table

[TABLE="class: grid, width: 518"]
<tbody>[TR]
[TD="width: 94, bgcolor: #B4C6E7"][/TD]
[TD="width: 106, bgcolor: #B4C6E7"][/TD]
[TD="width: 84, bgcolor: #B4C6E7"][/TD]
[TD="width: 100, bgcolor: #B4C6E7"][/TD]
[TD="width: 78, bgcolor: #B4C6E7"][/TD]
[TD="width: 116, bgcolor: #B4C6E7"][/TD]
[TD="width: 110, bgcolor: #B4C6E7"][/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]Natal House
[/TD]
[TD="bgcolor: #B4C6E7"]Start House(NZ)
[/TD]
[TD="bgcolor: #B4C6E7"]Degree Start
[/TD]
[TD="bgcolor: #B4C6E7"]End House(NZ)
[/TD]
[TD="bgcolor: #B4C6E7"]Degree End
[/TD]
[TD="bgcolor: #B4C6E7"]Degree Cum Start
[/TD]
[TD="bgcolor: #B4C6E7"]Degree Cum End
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]1
[/TD]
[TD="bgcolor: #B4C6E7"]10
[/TD]
[TD="bgcolor: #B4C6E7"]14256
[/TD]
[TD="bgcolor: #B4C6E7"]11
[/TD]
[TD="bgcolor: #B4C6E7"]100845
[/TD]
[TD="bgcolor: #B4C6E7"]2714256
[/TD]
[TD="bgcolor: #B4C6E7"]3100845
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]2
[/TD]
[TD="bgcolor: #B4C6E7"]11
[/TD]
[TD="bgcolor: #B4C6E7"]100845
[/TD]
[TD="bgcolor: #B4C6E7"]12
[/TD]
[TD="bgcolor: #B4C6E7"]172853
[/TD]
[TD="bgcolor: #B4C6E7"]3100845
[/TD]
[TD="bgcolor: #B4C6E7"]3472853
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]3
[/TD]
[TD="bgcolor: #B4C6E7"]12
[/TD]
[TD="bgcolor: #B4C6E7"]172853
[/TD]
[TD="bgcolor: #B4C6E7"]1
[/TD]
[TD="bgcolor: #B4C6E7"]181504
[/TD]
[TD="bgcolor: #B4C6E7"]3472853
[/TD]
[TD="bgcolor: #B4C6E7"]181504
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]4
[/TD]
[TD="bgcolor: #B4C6E7"]1
[/TD]
[TD="bgcolor: #B4C6E7"]181504
[/TD]
[TD="bgcolor: #B4C6E7"]2
[/TD]
[TD="bgcolor: #B4C6E7"]133110
[/TD]
[TD="bgcolor: #B4C6E7"]181504
[/TD]
[TD="bgcolor: #B4C6E7"]433110
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]5
[/TD]
[TD="bgcolor: #B4C6E7"]2
[/TD]
[TD="bgcolor: #B4C6E7"]133110
[/TD]
[TD="bgcolor: #B4C6E7"]3
[/TD]
[TD="bgcolor: #B4C6E7"]64122
[/TD]
[TD="bgcolor: #B4C6E7"]433110
[/TD]
[TD="bgcolor: #B4C6E7"]664122
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]6
[/TD]
[TD="bgcolor: #B4C6E7"]3
[/TD]
[TD="bgcolor: #B4C6E7"]64122
[/TD]
[TD="bgcolor: #B4C6E7"]4
[/TD]
[TD="bgcolor: #B4C6E7"]14256
[/TD]
[TD="bgcolor: #B4C6E7"]664122
[/TD]
[TD="bgcolor: #B4C6E7"]914256
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]7
[/TD]
[TD="bgcolor: #B4C6E7"]4
[/TD]
[TD="bgcolor: #B4C6E7"]14256
[/TD]
[TD="bgcolor: #B4C6E7"]5
[/TD]
[TD="bgcolor: #B4C6E7"]100845
[/TD]
[TD="bgcolor: #B4C6E7"]914256
[/TD]
[TD="bgcolor: #B4C6E7"]1300845
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]8
[/TD]
[TD="bgcolor: #B4C6E7"]5
[/TD]
[TD="bgcolor: #B4C6E7"]100845
[/TD]
[TD="bgcolor: #B4C6E7"]6
[/TD]
[TD="bgcolor: #B4C6E7"]172853
[/TD]
[TD="bgcolor: #B4C6E7"]1300845
[/TD]
[TD="bgcolor: #B4C6E7"]1672853
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]9
[/TD]
[TD="bgcolor: #B4C6E7"]6
[/TD]
[TD="bgcolor: #B4C6E7"]172853
[/TD]
[TD="bgcolor: #B4C6E7"]7
[/TD]
[TD="bgcolor: #B4C6E7"]181504
[/TD]
[TD="bgcolor: #B4C6E7"]1672853
[/TD]
[TD="bgcolor: #B4C6E7"]1981504
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]10
[/TD]
[TD="bgcolor: #B4C6E7"]7
[/TD]
[TD="bgcolor: #B4C6E7"]181504
[/TD]
[TD="bgcolor: #B4C6E7"]8
[/TD]
[TD="bgcolor: #B4C6E7"]133110
[/TD]
[TD="bgcolor: #B4C6E7"]1981504
[/TD]
[TD="bgcolor: #B4C6E7"]2233110
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]11
[/TD]
[TD="bgcolor: #B4C6E7"]8
[/TD]
[TD="bgcolor: #B4C6E7"]133110
[/TD]
[TD="bgcolor: #B4C6E7"]9
[/TD]
[TD="bgcolor: #B4C6E7"]64122
[/TD]
[TD="bgcolor: #B4C6E7"]2233110
[/TD]
[TD="bgcolor: #B4C6E7"]2464122
[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]12
[/TD]
[TD="bgcolor: #B4C6E7"]9
[/TD]
[TD="bgcolor: #B4C6E7"]64122
[/TD]
[TD="bgcolor: #B4C6E7"]10
[/TD]
[TD="bgcolor: #B4C6E7"]14256
[/TD]
[TD="bgcolor: #B4C6E7"]2464122
[/TD]
[TD="bgcolor: #B4C6E7"]2714256
[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if you need something else?

Regards
Rajaniesh

 

[thisoldman]

OK. Do not use any of the text to number conversion formulas. The lookup table is still in columns P & Q

Book1
	P	Q	Z	AA	AB
10	Lookup	House		Find	Answer
11	0	3		90000	3
12	181504	4		390000	4
13	433110	5		690000	6
14	664122	6		990000	7
15	914256	7		1290000	7
16	1300845	8		1590000	8
17	1672853	9		1890000	9
18	1981504	10		2190000	10
19	2233110	11		2390000	11
20	2464122	12		2690000	12
21	2714256	1		2990000	1
22	3100845	2		3290000	2
23	3472853	3		3595959	3
Sheet1

Cell Formulas
Range	Formula
AB11	=VLOOKUP($AA11, $P$11:$Q$23, 2, TRUE)

You can apply your custom formatting to the values in column P and it should still work.

 

[rajaniesh]

Sir,

It is still not working


[TABLE="class: grid, width: 1054"]
<tbody>[TR]
[TD]Degree Start (Decimal)
[/TD]
[TD]Degree End (Decimal)
[/TD]
[TD]Look Up value
[/TD]
[TD]Natal House
[/TD]
[TD]Lookup Value in Degree
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD="align: right"]2714256
[/TD]
[TD="align: right"]3100845
[/TD]
[TD][/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3100845
[/TD]
[TD="align: right"]3472853
[/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3472853
[/TD]
[TD="align: right"]181504
[/TD]
[TD][/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]181504
[/TD]
[TD="align: right"]433110
[/TD]
[TD][/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]433110
[/TD]
[TD="align: right"]664122
[/TD]
[TD="align: right"]2225320
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]222°53'20.00
[/TD]
[TD="align: right"]-10
[/TD]
[/TR]
[TR]
[TD="align: right"]664122
[/TD]
[TD="align: right"]914256
[/TD]
[TD][/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]914256
[/TD]
[TD="align: right"]1300845
[/TD]
[TD][/TD]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]1300845
[/TD]
[TD="align: right"]1672853
[/TD]
[TD="align: right"]2940640
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]294°06'40.00
[/TD]
[TD="align: right"]-12
[/TD]
[/TR]
[TR]
[TD="align: right"]1672853
[/TD]
[TD="align: right"]1981504
[/TD]
[TD="align: right"]3191320
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]319°13'20.00
[/TD]
[TD="align: right"]-12
[/TD]
[/TR]
[TR]
[TD="align: right"]1981504
[/TD]
[TD="align: right"]2233110
[/TD]
[TD="align: right"]92000
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]09°20'00.00
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]2233110
[/TD]
[TD="align: right"]2464122
[/TD]
[TD][/TD]
[TD="align: right"]11
[/TD]
[TD][/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]2464122
[/TD]
[TD="align: right"]2714256
[/TD]
[TD][/TD]
[TD="align: right"]12
[/TD]
[TD][/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

I truly cannot determine what you are doing differently from me.

You can download my working spreadsheet: https://www.dropbox.com/s/0az1rp8pba5ss5m/natal_house_lookup_for_rajaniesh.xlsx?dl=0

 

[rajaniesh]

Thank YOU sir,

Actually the issue is that I can not sort the lookup values and the spreadsheet shared by you does not depict the correct values. For example 3rd House is not zero. Let me share the spreadsheet here https://1drv.ms/f/s!Aubmyn_jlPhgiuZ3ncEHW0BF8UT7Vw I created a separate section for calculation called Mr Excel.

Regards
Rajaniesh

 

[thisoldman]

Oh! You do not want to set up a separate lookup table.

See if this works —
=LOOKUP($AA22,{0,181504,433110,664122,914256,1300845,1672853,1981504,2233110,2464122,2714256,3100845,3472853},{3,4,5,6,7,8,9,10,11,12,1,2,3})

 

[rajaniesh]

Sir,

What logic is used to keep 0 to 3rd house which is not correct. In this formula we need to put everything in the descending order for it to work but in real scenario it may not happen and it may not work if we keep it non sorted order.

Regards
Rajaniesh

 

[thisoldman]

I think I finally understand. The table in columns B through H will change; the values are not constant. We need to do a lookup with reference values that will change.

Natal House is in B8:B19.
Degree Cum End is in H8:H19.

The formula in K8 is copied downward.
M8 is a constant, zero.
The formula in M9 is copied downward.
N8 and N9 contain different formulas.
The formula in N9 is copied downward through N20.

Book1
	J	K	L	M	N
6	Tests			Lookup Table
7	Find	Result		Ranked DCM	House
8	90000	3		0	3
9	390000	4		181504	4
10	480000	5		433110	5
11	690000	6		664122	6
12	990000	7		914256	7
13	1290000	7		1300845	8
14	1590000	8		1672853	9
15	1890000	9		1981504	10
16	2190000	10		2233110	11
17	2490000	12		2464122	12
18	2790000	1		2714256	1
19	3090000	1		3100845	2
20	3390000	2		3472853	3
21	1	3
22	3595959	3
GeneralFormat

Cell Formulas
Range	Formula
K8	=VLOOKUP($J8,$M$8:$N$20,2,TRUE)
N8	=INDEX($B$8:$B$19, MATCH($M9, $H$8:$H$19, 0))
N9	=IF(N8=12, 1, N8 + 1)
M9	=SMALL($H$8:$H$19, ROW() - ROW($M$8))

https://www.dropbox.com/s/0o2ha8er2idcrkm/natal_house_lookup_for_rajaniesh_v2.xlsx?dl=0