| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the closest degree?
Does anyone have any suggestions on how to determine the closest degree?
Given condition 360 degree = 0 degree There is a list of degrees in row 1. 303(Q1), 10(R1), 310(S1), 310(T1), 170(U1), 351(V1), 324(W1) There is a given degree in cell O1 I would like to determine the closest degree, which is greater than and less than the given degree. Example one, if the given degree is 1, then it should return 10 in cell N1, because it is closest degree and greater than 1, and it should return 351 in cell M1, because it is closest degree and less than 1. Example two, if the given degree is 340, then it should return 351 in cell N1, because it is closest degree and greater than 340, and it should return 324 in cell M1, because it is closest degree and less than 340. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest degree?
well, the formula is somehow complicate, but it should match your requirement.
M1=IF(SMALL(Q1:W1,1)=O1,MAX(Q1:W1),LARGE(Q1:W1,RA NK(O1,O1:W1,0)+(COUNT(O1:W1)+1-RANK(O1,O1:W1,0)-RANK(O1,O1:W1,1)))) N1=IF(LARGE(Q1:W1,1)<=O1,MIN(Q1:W1),SMALL(Q1:W1,RA NK(O1,O1:W1,1)+(COUNT(O1:W1)+1-RANK(O1,O1:W1,0)-RANK(O1,O1:W1,1)))) "Eric" wrote: Does anyone have any suggestions on how to determine the closest degree? Given condition 360 degree = 0 degree There is a list of degrees in row 1. 303(Q1), 10(R1), 310(S1), 310(T1), 170(U1), 351(V1), 324(W1) There is a given degree in cell O1 I would like to determine the closest degree, which is greater than and less than the given degree. Example one, if the given degree is 1, then it should return 10 in cell N1, because it is closest degree and greater than 1, and it should return 351 in cell M1, because it is closest degree and less than 1. Example two, if the given degree is 340, then it should return 351 in cell N1, because it is closest degree and greater than 340, and it should return 324 in cell M1, because it is closest degree and less than 340. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closest degree?
M1:=SMALL(Q1:V1,MOD(COUNTIF(Q1:V1,"<"&O1)-1,COUNT(Q1:V1))+1)
N1:=SMALL(Q1:V1,MOD(COUNTIF(Q1:V1,"<"&O1),COUNT(Q1 :V1))+1) "Eric" wrote: Does anyone have any suggestions on how to determine the closest degree? Given condition 360 degree = 0 degree There is a list of degrees in row 1. 303(Q1), 10(R1), 310(S1), 310(T1), 170(U1), 351(V1), 324(W1) There is a given degree in cell O1 I would like to determine the closest degree, which is greater than and less than the given degree. Example one, if the given degree is 1, then it should return 10 in cell N1, because it is closest degree and greater than 1, and it should return 351 in cell M1, because it is closest degree and less than 1. Example two, if the given degree is 340, then it should return 351 in cell N1, because it is closest degree and greater than 340, and it should return 324 in cell M1, because it is closest degree and less than 340. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to determine the closest value? | Excel Discussion (Misc queries) | |||
| How to determine the closest value? | Excel Discussion (Misc queries) | |||
| Imputting an angle degree. Degree, minute, second | Excel Discussion (Misc queries) | |||
| Imputting an angle degree. Degree, minute, second | Excel Worksheet Functions | |||
| Convert decimal degree (lattitude/longitude) into Degree, | Excel Discussion (Misc queries) | |||
Linear Mode
