Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain val
I'm looking for a function or formula in Excel that will find a number in a
range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain val
Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
Thanks, that works great for the number closest to less than 10. I also need
the same type solution for the number closest to more than 10. For instance a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your formula only gets the first number under 10. How can I get a formula to give me the first number above 10. Like in this case I would need it to select the value in cell a2 (19.13). Keep in mind the values in these cells will vary depending on other calcs in the spreadsheet. Thanks again! "Scoops" wrote: Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
Try...
=INDEX(A1:A7,MATCH(10,A1:A7)+(LOOKUP(10,A1:A7)<10 )) Hope this helps! In article , Don wrote: Thanks, that works great for the number closest to less than 10. I also need the same type solution for the number closest to more than 10. For instance a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your formula only gets the first number under 10. How can I get a formula to give me the first number above 10. Like in this case I would need it to select the value in cell a2 (19.13). Keep in mind the values in these cells will vary depending on other calcs in the spreadsheet. Thanks again! "Scoops" wrote: Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
Hi!
Try this: This is an array formula and MUST be entered using the key combination of CTRL,SHIFT,ENTER: =MIN(IF(A1:A710,A1:A7)) Biff "Don" wrote in message ... Thanks, that works great for the number closest to less than 10. I also need the same type solution for the number closest to more than 10. For instance a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your formula only gets the first number under 10. How can I get a formula to give me the first number above 10. Like in this case I would need it to select the value in cell a2 (19.13). Keep in mind the values in these cells will vary depending on other calcs in the spreadsheet. Thanks again! "Scoops" wrote: Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
If there is an exact match that formula returns the exact match, not the
next higher value. Biff "Domenic" wrote in message ... Try... =INDEX(A1:A7,MATCH(10,A1:A7)+(LOOKUP(10,A1:A7)<10 )) Hope this helps! In article , Don wrote: Thanks, that works great for the number closest to less than 10. I also need the same type solution for the number closest to more than 10. For instance a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your formula only gets the first number under 10. How can I get a formula to give me the first number above 10. Like in this case I would need it to select the value in cell a2 (19.13). Keep in mind the values in these cells will vary depending on other calcs in the spreadsheet. Thanks again! "Scoops" wrote: Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
P.S.
Also, if the lookup_value is less than the lowest value in the range it returns #N/A. Biff "Biff" wrote in message ... If there is an exact match that formula returns the exact match, not the next higher value. Biff "Domenic" wrote in message ... Try... =INDEX(A1:A7,MATCH(10,A1:A7)+(LOOKUP(10,A1:A7)<10 )) Hope this helps! In article , Don wrote: Thanks, that works great for the number closest to less than 10. I also need the same type solution for the number closest to more than 10. For instance a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your formula only gets the first number under 10. How can I get a formula to give me the first number above 10. Like in this case I would need it to select the value in cell a2 (19.13). Keep in mind the values in these cells will vary depending on other calcs in the spreadsheet. Thanks again! "Scoops" wrote: Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
Hey thanks alot!!! I knew it was probably something fairly simple for an
Excel Guru! I know just enough to get in a bind! It works great..........thanks again to Scoops & Domenic. "Domenic" wrote: Try... =INDEX(A1:A7,MATCH(10,A1:A7)+(LOOKUP(10,A1:A7)<10 )) Hope this helps! In article , Don wrote: Thanks, that works great for the number closest to less than 10. I also need the same type solution for the number closest to more than 10. For instance a1=1.34, a2=19.13, a3=36.64, a4=42.98, a5=76.41, a6=91.22, a7=100. Your formula only gets the first number under 10. How can I get a formula to give me the first number above 10. Like in this case I would need it to select the value in cell a2 (19.13). Keep in mind the values in these cells will vary depending on other calcs in the spreadsheet. Thanks again! "Scoops" wrote: Don wrote: I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0 - 100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there. Thanks in advance for the help! Hi Don Try this in your result cell: =VLOOKUP(10,$A$1:$A$7,1,TRUE) Copy it down three cells and amend the 10 to 40, 50 and 90 respectively. Note that you can make the "10" a reference to another cell so that you could change that cell rather than amending the formula in the future. Regards Steve |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
In article ,
"Biff" wrote: If there is an exact match that formula returns the exact match, not the next higher value. Since VLOOKUP returned the desired results for the first part, I assumed that the reverse would be true for the second part. From the OP's response, it looks like assumed correctly. :) Also, if the lookup_value is less than the lowest value in the range it returns #N/A. Good point, Biff! Depending on what the OP is looking for, maybe some variation of the following... =IF(D2<"",IF(D2=A1,INDEX(A1:A7,MATCH(D2,A1:A7)+( LOOKUP(D2,A1:A7)<D2)), A1),"N/A") ....where D2 contains the lookup/target value. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
From the OP's response, it looks like [i] assumed correctly. :)
Yeah, I'm starting to get too nit-picky! Biff "Domenic" wrote in message ... In article , "Biff" wrote: If there is an exact match that formula returns the exact match, not the next higher value. Since VLOOKUP returned the desired results for the first part, I assumed that the reverse would be true for the second part. From the OP's response, it looks like assumed correctly. :) Also, if the lookup_value is less than the lowest value in the range it returns #N/A. Good point, Biff! Depending on what the OP is looking for, maybe some variation of the following... =IF(D2<"",IF(D2=A1,INDEX(A1:A7,MATCH(D2,A1:A7)+( LOOKUP(D2,A1:A7)<D2)), A1),"N/A") ...where D2 contains the lookup/target value. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for a # in a column of #'s that's closest to a certain
In article ,
"Biff" wrote: From the OP's response, it looks like [i] assumed correctly. :) Yeah, I'm starting to get too nit-picky! Biff Well, somebody has to keep us on our toes... :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula for a column | Excel Discussion (Misc queries) |