| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having problem with referencing
These are the actual values in a portion of a table; If cell "T1" says
".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second, since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Having problem with referencing
Hi Doug,
Is there an underlying pattern to these numbers? What is the likelyhood of T1 fitting exactly between two values, and what result do you want if it does? If T1 was 0.14235 would the answer be 0.33 or 0.4? Basically, we need some background information before any reply could be useful. "Doug" wrote in message ... These are the actual values in a portion of a table; If cell "T1" says ".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second, since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Having problem with referencing
This solves the problem as described, but my other questions are still
relevant. =INDEX($A$2:$A$13,MATCH($T$1,$B$2:$B$13,1))+INDEX( $B$1:$E$1, MATCH(MIN(INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B $2:$B$13,1),)-$T$1),)), INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B$2:$B$13,1 ),)-$T$1),),0)) Steve D. If T1 was "Steve Dunn" wrote in message ... Hi Doug, Is there an underlying pattern to these numbers? What is the likelyhood of T1 fitting exactly between two values, and what result do you want if it does? If T1 was 0.14235 would the answer be 0.33 or 0.4? Basically, we need some background information before any reply could be useful. "Doug" wrote in message ... These are the actual values in a portion of a table; If cell "T1" says ".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second, since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Having problem with referencing
To answer your question, reason for the confusion is, this is not the whole
table as it is a portion of the table. These values are from a Z distribution table. Here is the whole table, but don't know if it will fit? z 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.0 0.0000 0.0040 0.0080 0.0120 0.0160 0.0199 0.0239 0.0279 0.0319 0.0359 0.1 0.0398 0.0438 0.0478 0.0517 0.0557 0.0596 0.0636 0.0675 0.0714 0.0753 0.2 0.0793 0.0832 0.0871 0.0910 0.0948 0.0987 0.1026 0.1064 0.1103 0.1141 0.3 0.1179 0.1217 0.1255 0.1293 0.1331 0.1368 0.1406 0.1443 0.1480 0.1517 0.4 0.1554 0.1591 0.1628 0.1664 0.1700 0.1736 0.1772 0.1808 0.1844 0.1879 0.5 0.1915 0.1950 0.1985 0.2019 0.2054 0.2088 0.2123 0.2157 0.2190 0.2224 0.6 0.2257 0.2291 0.2324 0.2357 0.2389 0.2422 0.2454 0.2486 0.2517 0.2549 0.7 0.2580 0.2611 0.2642 0.2673 0.2704 0.2734 0.2764 0.2794 0.2823 0.2852 0.8 0.2881 0.2910 0.2939 0.2967 0.2995 0.3023 0.3051 0.3078 0.3106 0.3133 0.9 0.3159 0.3186 0.3212 0.3238 0.3264 0.3289 0.3315 0.3340 0.3365 0.3389 1.0 0.3413 0.3438 0.3461 0.3485 0.3508 0.3531 0.3554 0.3577 0.3599 0.3621 1.1 0.3643 0.3665 0.3686 0.3708 0.3729 0.3749 0.3770 0.3790 0.3810 0.3830 1.2 0.3849 0.3869 0.3888 0.3907 0.3925 0.3944 0.3962 0.3980 0.3997 0.4015 1.3 0.4032 0.4049 0.4066 0.4082 0.4099 0.4115 0.4131 0.4147 0.4162 0.4177 1.4 0.4192 0.4207 0.4222 0.4236 0.4251 0.4265 0.4279 0.4292 0.4306 0.4319 1.5 0.4332 0.4345 0.4357 0.4370 0.4382 0.4394 0.4406 0.4418 0.4429 0.4441 1.6 0.4452 0.4463 0.4474 0.4484 0.4495 0.4505 0.4515 0.4525 0.4535 0.4545 1.7 0.4554 0.4564 0.4573 0.4582 0.4591 0.4599 0.4608 0.4616 0.4625 0.4633 1.8 0.4641 0.4649 0.4656 0.4664 0.4671 0.4678 0.4686 0.4693 0.4699 0.4706 1.9 0.4713 0.4719 0.4726 0.4732 0.4738 0.4744 0.4750 0.4756 0.4761 0.4767 2.0 0.4772 0.4778 0.4783 0.4788 0.4793 0.4798 0.4803 0.4808 0.4812 0.4817 2.1 0.4821 0.4826 0.4830 0.4834 0.4838 0.4842 0.4846 0.4850 0.4854 0.4857 2.2 0.4861 0.4864 0.4868 0.4871 0.4875 0.4878 0.4881 0.4884 0.4887 0.4890 2.3 0.4893 0.4896 0.4898 0.4901 0.4904 0.4906 0.4909 0.4911 0.4913 0.4916 2.4 0.4918 0.4920 0.4922 0.4925 0.4927 0.4929 0.4931 0.4932 0.4934 0.4936 2.5 0.4938 0.4940 0.4941 0.4943 0.4945 0.4946 0.4948 0.4949 0.4951 0.4952 2.6 0.4953 0.4955 0.4956 0.4957 0.4959 0.4960 0.4961 0.4962 0.4963 0.4964 2.7 0.4965 0.4966 0.4967 0.4968 0.4969 0.4970 0.4971 0.4972 0.4973 0.4974 2.8 0.4974 0.4975 0.4976 0.4977 0.4977 0.4978 0.4979 0.4979 0.4980 0.4981 2.9 0.4981 0.4982 0.4982 0.4983 0.4984 0.4984 0.4985 0.4985 0.4986 0.4986 3.0 0.4987 0.4987 0.4987 0.4988 0.4988 0.4989 0.4989 0.4989 0.4990 0.4990 3.1 0.4990 0.4991 0.4991 0.4991 0.4992 0.4992 0.4992 0.4992 0.4993 0.4993 3.2 0.4993 0.4993 0.4994 0.4994 0.4994 0.4994 0.4994 0.4995 0.4995 0.4995 3.3 0.4995 0.4995 0.4995 0.4996 0.4996 0.4996 0.4996 0.4996 0.4996 0.4997 3.4 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4998 3.5 0.4998 4.0 0.49997 4.5 0.499997 5.0 0.4999997 -- Thank you! "Steve Dunn" wrote: This solves the problem as described, but my other questions are still relevant. =INDEX($A$2:$A$13,MATCH($T$1,$B$2:$B$13,1))+INDEX( $B$1:$E$1, MATCH(MIN(INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B $2:$B$13,1),)-$T$1),)), INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B$2:$B$13,1 ),)-$T$1),),0)) Steve D. If T1 was "Steve Dunn" wrote in message ... Hi Doug, Is there an underlying pattern to these numbers? What is the likelyhood of T1 fitting exactly between two values, and what result do you want if it does? If T1 was 0.14235 would the answer be 0.33 or 0.4? Basically, we need some background information before any reply could be useful. "Doug" wrote in message ... These are the actual values in a portion of a table; If cell "T1" says ".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second, since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Having problem with referencing
It's a monster, but it works!
=INDEX($A$2:$A$40,MATCH($T$1,$B$2:$B$40,1))+INDEX( $B$1:$K$1, MATCH(MIN(INDEX(ABS(OFFSET($B$1:$K$1,MATCH($T$1,$B $2:$B$40,1),)-$T$1),)), INDEX(ABS(OFFSET($B$1:$K$1,MATCH($T$1,$B$2:$B$40,1 ),)-$T$1),),0))+ IF(COUNT(OFFSET($B$1:$K$1,MATCH($T$1,$B$2:$B$40,1) ,0))1,0.01,0.5)* (ABS(INDEX($B$2:$B$40,MATCH($T$1,$B$2:$B$40,1)+1)-$T$1)< ABS(INDEX($B$2:$K$40,MATCH($T$1,$B$2:$B$40,1), COUNT(OFFSET($B$1:$K$1,MATCH($T$1,$B$2:$B$40,1),0) ))-$T$1)) "Doug" wrote in message ... To answer your question, reason for the confusion is, this is not the whole table as it is a portion of the table. These values are from a Z distribution table. Here is the whole table, but don't know if it will fit? z 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.0 0.0000 0.0040 0.0080 0.0120 0.0160 0.0199 0.0239 0.0279 0.0319 0.0359 0.1 0.0398 0.0438 0.0478 0.0517 0.0557 0.0596 0.0636 0.0675 0.0714 0.0753 0.2 0.0793 0.0832 0.0871 0.0910 0.0948 0.0987 0.1026 0.1064 0.1103 0.1141 0.3 0.1179 0.1217 0.1255 0.1293 0.1331 0.1368 0.1406 0.1443 0.1480 0.1517 0.4 0.1554 0.1591 0.1628 0.1664 0.1700 0.1736 0.1772 0.1808 0.1844 0.1879 0.5 0.1915 0.1950 0.1985 0.2019 0.2054 0.2088 0.2123 0.2157 0.2190 0.2224 0.6 0.2257 0.2291 0.2324 0.2357 0.2389 0.2422 0.2454 0.2486 0.2517 0.2549 0.7 0.2580 0.2611 0.2642 0.2673 0.2704 0.2734 0.2764 0.2794 0.2823 0.2852 0.8 0.2881 0.2910 0.2939 0.2967 0.2995 0.3023 0.3051 0.3078 0.3106 0.3133 0.9 0.3159 0.3186 0.3212 0.3238 0.3264 0.3289 0.3315 0.3340 0.3365 0.3389 1.0 0.3413 0.3438 0.3461 0.3485 0.3508 0.3531 0.3554 0.3577 0.3599 0.3621 1.1 0.3643 0.3665 0.3686 0.3708 0.3729 0.3749 0.3770 0.3790 0.3810 0.3830 1.2 0.3849 0.3869 0.3888 0.3907 0.3925 0.3944 0.3962 0.3980 0.3997 0.4015 1.3 0.4032 0.4049 0.4066 0.4082 0.4099 0.4115 0.4131 0.4147 0.4162 0.4177 1.4 0.4192 0.4207 0.4222 0.4236 0.4251 0.4265 0.4279 0.4292 0.4306 0.4319 1.5 0.4332 0.4345 0.4357 0.4370 0.4382 0.4394 0.4406 0.4418 0.4429 0.4441 1.6 0.4452 0.4463 0.4474 0.4484 0.4495 0.4505 0.4515 0.4525 0.4535 0.4545 1.7 0.4554 0.4564 0.4573 0.4582 0.4591 0.4599 0.4608 0.4616 0.4625 0.4633 1.8 0.4641 0.4649 0.4656 0.4664 0.4671 0.4678 0.4686 0.4693 0.4699 0.4706 1.9 0.4713 0.4719 0.4726 0.4732 0.4738 0.4744 0.4750 0.4756 0.4761 0.4767 2.0 0.4772 0.4778 0.4783 0.4788 0.4793 0.4798 0.4803 0.4808 0.4812 0.4817 2.1 0.4821 0.4826 0.4830 0.4834 0.4838 0.4842 0.4846 0.4850 0.4854 0.4857 2.2 0.4861 0.4864 0.4868 0.4871 0.4875 0.4878 0.4881 0.4884 0.4887 0.4890 2.3 0.4893 0.4896 0.4898 0.4901 0.4904 0.4906 0.4909 0.4911 0.4913 0.4916 2.4 0.4918 0.4920 0.4922 0.4925 0.4927 0.4929 0.4931 0.4932 0.4934 0.4936 2.5 0.4938 0.4940 0.4941 0.4943 0.4945 0.4946 0.4948 0.4949 0.4951 0.4952 2.6 0.4953 0.4955 0.4956 0.4957 0.4959 0.4960 0.4961 0.4962 0.4963 0.4964 2.7 0.4965 0.4966 0.4967 0.4968 0.4969 0.4970 0.4971 0.4972 0.4973 0.4974 2.8 0.4974 0.4975 0.4976 0.4977 0.4977 0.4978 0.4979 0.4979 0.4980 0.4981 2.9 0.4981 0.4982 0.4982 0.4983 0.4984 0.4984 0.4985 0.4985 0.4986 0.4986 3.0 0.4987 0.4987 0.4987 0.4988 0.4988 0.4989 0.4989 0.4989 0.4990 0.4990 3.1 0.4990 0.4991 0.4991 0.4991 0.4992 0.4992 0.4992 0.4992 0.4993 0.4993 3.2 0.4993 0.4993 0.4994 0.4994 0.4994 0.4994 0.4994 0.4995 0.4995 0.4995 3.3 0.4995 0.4995 0.4995 0.4996 0.4996 0.4996 0.4996 0.4996 0.4996 0.4997 3.4 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4998 3.5 0.4998 4.0 0.49997 4.5 0.499997 5.0 0.4999997 -- Thank you! "Steve Dunn" wrote: This solves the problem as described, but my other questions are still relevant. =INDEX($A$2:$A$13,MATCH($T$1,$B$2:$B$13,1))+INDEX( $B$1:$E$1, MATCH(MIN(INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B $2:$B$13,1),)-$T$1),)), INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B$2:$B$13,1 ),)-$T$1),),0)) Steve D. If T1 was "Steve Dunn" wrote in message ... Hi Doug, Is there an underlying pattern to these numbers? What is the likelyhood of T1 fitting exactly between two values, and what result do you want if it does? If T1 was 0.14235 would the answer be 0.33 or 0.4? Basically, we need some background information before any reply could be useful. "Doug" wrote in message ... These are the actual values in a portion of a table; If cell "T1" says ".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second, since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Having problem with referencing
Thank you so very much! This solved it. Now I can move on.
-- Thank you! "Steve Dunn" wrote: It's a monster, but it works! =INDEX($A$2:$A$40,MATCH($T$1,$B$2:$B$40,1))+INDEX( $B$1:$K$1, MATCH(MIN(INDEX(ABS(OFFSET($B$1:$K$1,MATCH($T$1,$B $2:$B$40,1),)-$T$1),)), INDEX(ABS(OFFSET($B$1:$K$1,MATCH($T$1,$B$2:$B$40,1 ),)-$T$1),),0))+ IF(COUNT(OFFSET($B$1:$K$1,MATCH($T$1,$B$2:$B$40,1) ,0))1,0.01,0.5)* (ABS(INDEX($B$2:$B$40,MATCH($T$1,$B$2:$B$40,1)+1)-$T$1)< ABS(INDEX($B$2:$K$40,MATCH($T$1,$B$2:$B$40,1), COUNT(OFFSET($B$1:$K$1,MATCH($T$1,$B$2:$B$40,1),0) ))-$T$1)) "Doug" wrote in message ... To answer your question, reason for the confusion is, this is not the whole table as it is a portion of the table. These values are from a Z distribution table. Here is the whole table, but don't know if it will fit? z 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.0 0.0000 0.0040 0.0080 0.0120 0.0160 0.0199 0.0239 0.0279 0.0319 0.0359 0.1 0.0398 0.0438 0.0478 0.0517 0.0557 0.0596 0.0636 0.0675 0.0714 0.0753 0.2 0.0793 0.0832 0.0871 0.0910 0.0948 0.0987 0.1026 0.1064 0.1103 0.1141 0.3 0.1179 0.1217 0.1255 0.1293 0.1331 0.1368 0.1406 0.1443 0.1480 0.1517 0.4 0.1554 0.1591 0.1628 0.1664 0.1700 0.1736 0.1772 0.1808 0.1844 0.1879 0.5 0.1915 0.1950 0.1985 0.2019 0.2054 0.2088 0.2123 0.2157 0.2190 0.2224 0.6 0.2257 0.2291 0.2324 0.2357 0.2389 0.2422 0.2454 0.2486 0.2517 0.2549 0.7 0.2580 0.2611 0.2642 0.2673 0.2704 0.2734 0.2764 0.2794 0.2823 0.2852 0.8 0.2881 0.2910 0.2939 0.2967 0.2995 0.3023 0.3051 0.3078 0.3106 0.3133 0.9 0.3159 0.3186 0.3212 0.3238 0.3264 0.3289 0.3315 0.3340 0.3365 0.3389 1.0 0.3413 0.3438 0.3461 0.3485 0.3508 0.3531 0.3554 0.3577 0.3599 0.3621 1.1 0.3643 0.3665 0.3686 0.3708 0.3729 0.3749 0.3770 0.3790 0.3810 0.3830 1.2 0.3849 0.3869 0.3888 0.3907 0.3925 0.3944 0.3962 0.3980 0.3997 0.4015 1.3 0.4032 0.4049 0.4066 0.4082 0.4099 0.4115 0.4131 0.4147 0.4162 0.4177 1.4 0.4192 0.4207 0.4222 0.4236 0.4251 0.4265 0.4279 0.4292 0.4306 0.4319 1.5 0.4332 0.4345 0.4357 0.4370 0.4382 0.4394 0.4406 0.4418 0.4429 0.4441 1.6 0.4452 0.4463 0.4474 0.4484 0.4495 0.4505 0.4515 0.4525 0.4535 0.4545 1.7 0.4554 0.4564 0.4573 0.4582 0.4591 0.4599 0.4608 0.4616 0.4625 0.4633 1.8 0.4641 0.4649 0.4656 0.4664 0.4671 0.4678 0.4686 0.4693 0.4699 0.4706 1.9 0.4713 0.4719 0.4726 0.4732 0.4738 0.4744 0.4750 0.4756 0.4761 0.4767 2.0 0.4772 0.4778 0.4783 0.4788 0.4793 0.4798 0.4803 0.4808 0.4812 0.4817 2.1 0.4821 0.4826 0.4830 0.4834 0.4838 0.4842 0.4846 0.4850 0.4854 0.4857 2.2 0.4861 0.4864 0.4868 0.4871 0.4875 0.4878 0.4881 0.4884 0.4887 0.4890 2.3 0.4893 0.4896 0.4898 0.4901 0.4904 0.4906 0.4909 0.4911 0.4913 0.4916 2.4 0.4918 0.4920 0.4922 0.4925 0.4927 0.4929 0.4931 0.4932 0.4934 0.4936 2.5 0.4938 0.4940 0.4941 0.4943 0.4945 0.4946 0.4948 0.4949 0.4951 0.4952 2.6 0.4953 0.4955 0.4956 0.4957 0.4959 0.4960 0.4961 0.4962 0.4963 0.4964 2.7 0.4965 0.4966 0.4967 0.4968 0.4969 0.4970 0.4971 0.4972 0.4973 0.4974 2.8 0.4974 0.4975 0.4976 0.4977 0.4977 0.4978 0.4979 0.4979 0.4980 0.4981 2.9 0.4981 0.4982 0.4982 0.4983 0.4984 0.4984 0.4985 0.4985 0.4986 0.4986 3.0 0.4987 0.4987 0.4987 0.4988 0.4988 0.4989 0.4989 0.4989 0.4990 0.4990 3.1 0.4990 0.4991 0.4991 0.4991 0.4992 0.4992 0.4992 0.4992 0.4993 0.4993 3.2 0.4993 0.4993 0.4994 0.4994 0.4994 0.4994 0.4994 0.4995 0.4995 0.4995 3.3 0.4995 0.4995 0.4995 0.4996 0.4996 0.4996 0.4996 0.4996 0.4996 0.4997 3.4 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4997 0.4998 3.5 0.4998 4.0 0.49997 4.5 0.499997 5.0 0.4999997 -- Thank you! "Steve Dunn" wrote: This solves the problem as described, but my other questions are still relevant. =INDEX($A$2:$A$13,MATCH($T$1,$B$2:$B$13,1))+INDEX( $B$1:$E$1, MATCH(MIN(INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B $2:$B$13,1),)-$T$1),)), INDEX(ABS(OFFSET($B$1:$E$1,MATCH($T$1,$B$2:$B$13,1 ),)-$T$1),),0)) Steve D. If T1 was "Steve Dunn" wrote in message ... Hi Doug, Is there an underlying pattern to these numbers? What is the likelyhood of T1 fitting exactly between two values, and what result do you want if it does? If T1 was 0.14235 would the answer be 0.33 or 0.4? Basically, we need some background information before any reply could be useful. "Doug" wrote in message ... These are the actual values in a portion of a table; If cell "T1" says ".12" I need it to first recognize the closest value, being between 0.1179 & 0.1217. Second, since it is closer to 0.1217 it would return the value ".31". How can this be accomplished please? 0.00 (0.01) 0.02 0.03 0.0 0.0000 0.0040 0.0080 0.0120 0.1 0.0398 0.0438 0.0478 0.0517 0.2 0.0793 0.0832 0.0871 0.0910 (0.3) 0.1179 (0.1217) 0.1255 0.1293 0.4 0.1554 0.1591 0.1628 0.1664 0.5 0.1915 0.1950 0.1985 0.2019 0.6 0.2257 0.2291 0.2324 0.2357 0.7 0.2580 0.2611 0.2642 0.2673 0.8 0.2881 0.2910 0.2939 0.2967 0.9 0.3159 0.3186 0.3212 0.3238 1.0 0.3413 0.3438 0.3461 0.3485 1.1 0.3643 0.3665 0.3686 0.3708 -- Thank you! . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Having problem with referencing
You're welcome Doug, glad to help.
"Doug" wrote in message ... Thank you so very much! This solved it. Now I can move on. -- Thank you! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB syntax problem with referencing another tab | Excel Discussion (Misc queries) | |||
| Referencing Date problem | Excel Discussion (Misc queries) | |||
| workbook referencing problem | Excel Worksheet Functions | |||
| Problem Referencing Multiple Lists | Excel Worksheet Functions | |||
| Referencing Problem | Excel Discussion (Misc queries) | |||
Linear Mode
