Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
Does anyone have any suggestions on how to determine the values from a list?
such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
try this for a start
C1=VLOOKUP($B$1-10,$A$1:$A$7,1) C2=IF(VLOOKUP($B$1,$A$1:$A$7,1,1)=B1,VLOOKUP($B$1, $A$1:$A$7,1,1),"") C3=VLOOKUP($B$1+10,$A$1:$A$7,1) -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Eric" wrote: Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
In C1:
=IF(COUNTIF(A1:A7,B1),INDEX(A1:A7,MATCH(LOOKUP(B1, A1:A7),A1:A7)-1),LOOKUP(B1,A1:A7)) In C2: =IF(ISNA(VLOOKUP(B1,A1:A7,1,0)),"",VLOOKUP(B1,A1:A 7,1,0)) In C3: =INDEX(A1:A7,MATCH(LOOKUP(B1,A1:A7),A1:A7)+1) "Eric" wrote: Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
Your formula in C1 is FAILED
If OP enter 35 in B1, the result of your formula returns 20, OP wants cell C1 returns to 30 C1=VLOOKUP($B$1-10,$A$1:$A$7,1) "John" wrote: try this for a start C1=VLOOKUP($B$1-10,$A$1:$A$7,1) C2=IF(VLOOKUP($B$1,$A$1:$A$7,1,1)=B1,VLOOKUP($B$1, $A$1:$A$7,1,1),"") C3=VLOOKUP($B$1+10,$A$1:$A$7,1) -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Eric" wrote: Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
I put an extra zero in by mistake - try C1=VLOOKUP($B$1-1,$A$1:$A$7,1)
-- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Teethless mama" wrote: Your formula in C1 is FAILED If OP enter 35 in B1, the result of your formula returns 20, OP wants cell C1 returns to 30 C1=VLOOKUP($B$1-10,$A$1:$A$7,1) "John" wrote: try this for a start C1=VLOOKUP($B$1-10,$A$1:$A$7,1) C2=IF(VLOOKUP($B$1,$A$1:$A$7,1,1)=B1,VLOOKUP($B$1, $A$1:$A$7,1,1),"") C3=VLOOKUP($B$1+10,$A$1:$A$7,1) -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Eric" wrote: Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
What should happen if/when the lookup number is less than the lowest number
in the list? What should happen if/when the lookup number is greater than the highest number in the list? What if the lookup number equals the lowest number in the list? What if the lookup number equals the highest number in the list? Biff "Eric" wrote in message ... Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
Although the OP didn't define what to do in all cases (see my other reply)
the formula in C1 could fail and the formula in C3 could fail. If the lookup number was 70 the formula in C3 will return a #REF! error. In C2: =IF(ISNA(VLOOKUP(B1,A1:A7,1,0)),"",VLOOKUP(B1,A1:A 7,1,0)) Why do a double lookup? =IF(COUNTIF(A1:A7,B1),B1,"") Biff "Teethless mama" wrote in message ... In C1: =IF(COUNTIF(A1:A7,B1),INDEX(A1:A7,MATCH(LOOKUP(B1, A1:A7),A1:A7)-1),LOOKUP(B1,A1:A7)) In C2: =IF(ISNA(VLOOKUP(B1,A1:A7,1,0)),"",VLOOKUP(B1,A1:A 7,1,0)) In C3: =INDEX(A1:A7,MATCH(LOOKUP(B1,A1:A7),A1:A7)+1) "Eric" wrote: Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
Thank everyone very mcuh for suggestions
I will set the 999999 as the highest number in the list, and set 0 as the lowest number. Therefore the given number in cell B1 must be within this range. Thank everyone very much Eric "T. Valko" wrote: What should happen if/when the lookup number is less than the lowest number in the list? What should happen if/when the lookup number is greater than the highest number in the list? What if the lookup number equals the lowest number in the list? What if the lookup number equals the highest number in the list? Biff "Eric" wrote in message ... Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the values from a list?
the given number in cell B1 must be within this range.
You might want to set data validation on cell B1 to ensure the correct value is entered. Select cell B1 Goto the menu DataValidation Allow: Custom Formula: =AND(B1A1,B1<A7) OK out Formulas: C1: =LOOKUP(B1,A1:A7,A1:A7) C2: =IF(COUNTIF(A1:A7,B1),B1,"") C3: =IF(B1=0,0,INDEX(A1:A7,MATCH(LOOKUP(B1,A1:A7),A1:A 7)+1)) Biff "Eric" wrote in message ... Thank everyone very mcuh for suggestions I will set the 999999 as the highest number in the list, and set 0 as the lowest number. Therefore the given number in cell B1 must be within this range. Thank everyone very much Eric "T. Valko" wrote: What should happen if/when the lookup number is less than the lowest number in the list? What should happen if/when the lookup number is greater than the highest number in the list? What if the lookup number equals the lowest number in the list? What if the lookup number equals the highest number in the list? Biff "Eric" wrote in message ... Does anyone have any suggestions on how to determine the values from a list? such as following example Under column A There are some values listed from cell A1 to A7 10,20,30,40,50,60,70 In cell B1, there is a given value 35, I need to determine the upper and lower values based on the given one. In cell C1, it returns 30 and in cell C3, it returns 40. If the given value from B1 is 40, then In cell C1, it returns 30, and in cell C2, it returns 40 and in cell C3, it returns 50. For the cell C1, it displays the lower value based on the given one. For the cell C2, it displays the given value if it equals to the given one, else display nothing. For the cell C3, it displays the upper value based on the given one. Does anyone have any suggestions on how to determine the values from a list? Thank for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the values from a list? | Excel Worksheet Functions | |||
How to determine the values from a list? | Excel Discussion (Misc queries) | |||
How to determine the values from a list? | Excel Worksheet Functions | |||
How to determine geometric mean of 152 values? | Excel Worksheet Functions | |||
List ? - How do I make information in one cell determine list to u | Excel Worksheet Functions |