Calculation For Closest Sites
Sep 22, 2009
I have a list of 50+ sites, I have made a table to show how close they are in relation to each other.
The black cells show when it is the same site the bright green show when they are <5 km away and light green shows when <15 km away.
I want to produce a list for each of the sites for the closest 10 sites.
Can anyone think of a way of doing this?
I can't upload a file as my work computer wont let me - it is a simple grid with site names in column a and row 1. then the distance between the site, in the same format as a fixture grid.
I am using office 2003.
View 7 Replies
ADVERTISEMENT
Oct 9, 2009
I am trying to import data into Excel from a webpage using the web query function; however, the webpage requires a login and password. I have the required login and password, but I cannot figure out how to make excel automatically input these into the correct fields and login in order to import the data.
Since I am trying to make all of our databases update automatically, I don't want to enter this info everyday when the macro runs.
View 9 Replies
View Related
Apr 28, 2009
i have 2 columns of data.
Column A is state
Column B is money owed to that state
So lets say
A1 is florida and B1 is 29,000
I am trying for column C to tell me what values (and if possible the cooresponding state) are the 2 closest values above it and the 2 closest values below it in B1:B50. It can appear as a string of text like 27,000 CA 28944 (OR) 31000 (FL) 31200 (GA)
View 9 Replies
View Related
Dec 6, 2012
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
Using :
MS Excel 2010
IE Explorer 8.0
View 1 Replies
View Related
Oct 23, 2009
I have the following table
A B
1000 610
900
800
700
600
500
400
300
200
100
I have a formula
=INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-B1)),ABS(A1:A10-B1),0))
which gives me a ans of 600.
My request is: how do I find out the next closest value.
eg. closest is 600 , the next closest is 500.
How can I find out the next closest value (500) ?
View 9 Replies
View Related
May 14, 2009
I'm looking to try and find the ten closest markets to a certain market and having trouble trying to break it down. once finding the closest ten from the annual sales, either bigger or smaller I'll be linking it to another sheet to automatically update the other sheet....
View 9 Replies
View Related
Aug 24, 2009
I have a list of numbers Example:
8.325,8.2,8.075,7.95,7.825,7.7,7.575,7.45,7.325,7.2,7.075,6.95,6.825,6.7,6.575,6.45,6.325,6.2,6.075,5.95,5.825,5.7,5.575 ,5.45,5.325 ect
Then I have a formula solving for a number, but I may get 6.00, which is not in my list. I want to have a "seek down" and display 5.95 (as it is in the list) and not 6.00. I will always seek for a lower number. Is this possible with a formula.
View 9 Replies
View Related
Jan 29, 2010
I have about 10000 datas in column A which are items' description.
Now I have an Item description in B2 & I want the closest match in C2 with B2 from Column A.
View 9 Replies
View Related
Oct 2, 2007
I have a list of standard resistor values on a separate tab and I want to grab the closest one (either lower or higher, whichever is closest). If I calculate a number to be 8.9, I want it to grab 9.1 instead of 8.2 (which is what it's doing with VLOOKUP). Is there an easy way to find the minimum distance or something?
View 5 Replies
View Related
Nov 3, 2006
I am looking for a fourmla to return a value in a column next to the closest value. Yes, this would normally be a vlookup example however in my application the numbers will not match exact as such in an client lookup application. I need to find the closest value to the value compared in the range and return the value to the right of it.
I have made up a small table attached with comparing what vlookup gives me and what I am looking for.
Example.xls
View 7 Replies
View Related
May 22, 2014
I am looking for how to find a closest match for to a a given number in a table based on looking up the value of one cell to determine what column to look in, then compare the given number to the values in the column, and then return the value in a another column of the same row it finds the match in. Ive attached an d example.
The value in B2 is the value I am trying to find a closest match to. It first must determine which column to look in, and that is dictated by cell B3. So, since R-404a is entered in B3, column J is the column it looks in, and it returns 0.21, since it is the closest match .
View 3 Replies
View Related
Dec 15, 2013
I have 2 lists. One list consists of the names of agencies as they would appear in our company's data base. The other list is the official business names. I would like match our company list with the official business name list by matching at least 2 words from the name with a name that shows up in column C (Official agency name).
If possible, I would like to ignore preposition and article words since they are used often in agency names.
I am looking for a formula to do this
View 3 Replies
View Related
Jul 6, 2008
I have a range of date values that span B1:BA1 and I need to find the next date value greater than TODAY() and return the column number.
View 14 Replies
View Related
Oct 30, 2009
I am working on payment/cost estimator and am looking for some ideas and formula or macro help. Basically what I am trying to do is set up a calculator where you would input the payment you could afford and it will tell you how much you can spend on the equipment.
The problems I am running into are that when you factor in the interest rate and commission, the payments are round numbers so I need the formula to find the closest value.
Example- $250 payment. The payments in the table w/ the interest rates are $158, $248 and $350. I would need it to pick the corresponding equipment cost for the $248 payment.
Make sense?
If a macro would be easier I am fine with that, just have limited experience setting one up.
View 9 Replies
View Related
Dec 4, 2009
I need to find which number in a row would be closest to zero, then display the associated name. The number can be negative. I used this, which works well if all the numbers are positive: =INDEX($C$1:$E$1,MATCH(MIN(C66:E66),C66:E66,0)). I'm running into a problem when the closest number is negative.
View 2 Replies
View Related
Jun 29, 2006
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.
View 10 Replies
View Related
May 6, 2009
In the attached sheet, I am using !Staff times columns N and O to nominate a mid point break time relevant to the start,lunch break and finish times of a shift.
What I would like to do is match the result to the closest time from !Race Times Column D (highlighted green). So if for example the mid point of the shift is 10:37 (as in !Staff Times cell N23), it actually comes back with the !Race Times result of 10:35 which is the closest actual race.
Ideally I would like it to look both sides of the midpoint time until it came to the closest match, rather than always looking at an earlier time, or always a later time (eg the cell N79 has a value of 11:36, however the 2 race times are 11:35 and 11:40. Preference is it shows 11:35). If however the N79 result was 11:38 or 11:39, I would prefer it went to 11:40).
However if this is not possible then picking the next time in either direction will do.
View 4 Replies
View Related
Aug 14, 2013
Say i had a col of random numbers, and their corresponding probabilities of occurring;
-32.33
0.001497
-35
0.001523
-32.06876
0.001551
-29
0.001579
10
0.001607
7
0.001636
-31.54628
0.001665
-18
0.001695
Neglect the nature of the sample, its just gibberish and not of concern here. The question is, if i enter a value in column C, how do i find the closest corresponding number to it in col A, and subsequently return its nearest corresponding probability from col B? For example, say i entered 8 in C1, then the formula would need to find the closest number to 8 in col A, which is 7, and return from col B the value of 0.001636.
View 1 Replies
View Related
May 8, 2014
I have a spreadsheet with column headings in Row 1, Column A-E of 10, 20, 30, 40, 50
I have values in Row 2 corresponding to the columns above of 25, 35, 45, 55, 65
in Row 2, column F, I have a value of 53.
What I need to do is look up the closest value to 53 in Row 2, but the lower of the value and place in G2.
For example, if I lookup 53 in Row 2, I want it to look at 45, and bring back the column heading of 30, and place in G2.
View 1 Replies
View Related
Feb 12, 2008
The formula you see below works great. It will lookup to the closest value that matches the criteria.
=LOOKUP(2*F2-0.000000000001,C1:C4+C2:C5,D2:D5)
Here is the problem when I try to expand the range I get a zero.
=LOOKUP(2*F2-0.000000000001,C1:C19+C2:C20,D2:D20)
Is there anyway we could overcome this?
I know I could use a formula array for this, but I would like to know if this could be done in a non-array formula ....
View 9 Replies
View Related
Mar 3, 2009
I need to scan a list of numbers and find the nearest number to say 80... ex
1
50
23
43
79
102
The formula would tell me the closest number is 79.
View 9 Replies
View Related
Mar 18, 2009
I'm trying to vlookup age in column A, then find the closest match of that row, then return the top row's column value of that closes match(largest value less than or equal to). looking for a formula to accomplish this.
For example, for age of 1month, I have a value of 7.5. So, I'd like to return: "5th"
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Age (in months)3rd5th10th25th205.1928815.570876.1151876.94590830.56.1719496.5359487.0757587.930901417.0704867.4290337.9697898.84744751.57.9690238.3221178.863829.763994Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jun 17, 2009
I've got a vlookup function driven by the result of a simple equation.
=IF(C36="","---",VLOOKUP(C18/C39,'Engine & Configuration'!D1:G24,2,TRUE))
The issue I have is that I want the result to return the CLOSEST match, not an exact or rounded up match. For example, is my "C18/C39" equation returns a value of 1.000039, I want it to match with "1" in the array and not with 1.25. The FALSE option will not work because exact matches will be very rare.
The values in the array are not whole numbers, so I don't believe a ROUNDUP type code will help.
View 9 Replies
View Related
Nov 3, 2006
I am looking for a fourmla to return a value in a column next to the closest value. Yes, this would normally be a vlookup example however in my application the numbers will not match exact as such in an client lookup application. I need to find the closest value to the value compared in the range and return the value to the right of it. I have made up a small table attached with comparing what vlookup gives me and what I am looking for.
View 6 Replies
View Related
Jul 24, 2007
I am trying to pull the closest times above and below and some other data from various worksheets. I've included a sample worksheet that has a dumbed down version of the tables and sheets.
I am having problems pulling the correct times and believe my error is in this part of the bigger formulas (
COLUMN(INDIRECT(portlookup&C5&"HEAD"))-COLUMN(C1),3)+1<=2
which breaks down to
COLUMN(INDIRECT(ABYJANHEAD))-COLUMN(C1),3)+1<=2
I don't think this is accurately telling my formula how to look at the table and pick the right time. I've tried different mod divisors and still can't get it to pull the right time.
View 9 Replies
View Related
Mar 5, 2008
I have a worksheet in which column C is a list of times in 5 minute increments. I want to activate in VBA the cell closest to noon but I can't tell it ("it" being my computer) to just find "12:00 PM" because the actual value may be 12:01, 12:02, 12:03, or 12:04. Is there a way to tell it to activate whichever value is there?
View 5 Replies
View Related
Apr 9, 2008
I've been turning this problem over it my head for the last day and can't seem to come up with a decent solution, so I figured I'd post it here to see if anybody else had any bright ideas.
I have a data sheet with a dropdown list on it to select the month and year that determines how the rest of the data sheet populates. Basically I'm giving weekly breakdowns of data for a month at a time, with the "Week Ending" heading (which are dates - Saturdays), populating off of the month and year selected from the dropdown.
So, to give an example, if somebody selects March 2008 in the dropdown, it will populate the week ending dates of 3/1, 3/8, 3/15, 3/22, 3/29, and then pull out the specific data for each week based on those dates.
What I'd like to do is write a formula that will find the first Saturday of the month. The way I have it jury-rigged right now is a list of months with the first Saturday listed next to it and a vlookup to grab the date of the first Saturday. I envision using this report indefinitely into the future and I'd like to avoid having to keep adding/changing the manual list of Saturdays, instead preferring to have a way to determine the date.
View 9 Replies
View Related
Mar 2, 2009
I have four Columns A, B, C, D. I need to pull back the value in B when the following is met:
A is the same value Closest value in column C to Column D.
View 3 Replies
View Related
Jun 30, 2009
Got an equation nightmare on my hands.
So i have 3 columns of data
A: State Abbreviate
B: Benchmark Number
C: New Number
I am trying to get an equation that in column D will return the 2 .....
View 11 Replies
View Related
Dec 8, 2009
I'm using vlookup to get a price for a unit of measure and it passes the value of the lesser not the greater. Is there anyway to get vlookup to pick the closest match as the greater value not the lesser one?
For example:
1000 units costs $8,000
4000 units cost $23,000
12000 units cost $68,000
If someone wants to buy 2500 units, I'd like the vlookup to pass the price based on 4000 units, but it passes 8,000 the value on 1000 units.
Can I use vlookup to give me the result of the greater unit not the lesser?
View 4 Replies
View Related
