|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 30th, 2010, 10:18 PM
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
May 4th, 2010, 02:07 PM
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 news  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
May 4th, 2010, 02:50 PM
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 news 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
May 5th, 2010, 01:31 PM
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 news 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
May 5th, 2010, 09:10 PM
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 news
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
May 7th, 2010, 09:28 PM
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 news
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
May 8th, 2010, 02:20 PM
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
