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 .....
I have a list with random ordered numbers (each number in a cell of a row): 1, 2, 6, 8, 11, 12, 16...
On the other hand I get a value (x). I want a formula that:
- If x is in the list, returns x
- If x is not in the list, returns the next higher value. For example, if x=8, returns 11
I want also a variation of the same formula, that returns the next lower value. For example, if x=8, returns 6.
Note that x will never be smaller than the first number on the list or higher than the highest number in the list.
I can't construct a formula that would do it for me. I have a row of numbers. I need to find the first number in that row that is greater or equal to 90, when that is found I need to return a number from a different row. Example:
I have 90 in A1.
In row B2:F2, I have numbers like -125, -100, -90, 90, 91, 150 etc.
In row B1:F1 I have rates like 4.25, 5.5, 3.75, 5.625, 6.00, 7.00 etc
I need to get the first number that is >=90, and return the rate above that number. ( Rates are always in one row)
I have a approx. 70 excel sheets with thousands of entries. Within a single sheet there are some duplicate entries (based on an account number). I find the duplicate entries by using the excel 2007 conditional formating and then sort by color to only show the duplicate entries. There are often hundreds of accounts that are duplicates. Sometimes there are more than 2 identical account duplicates (could be 3,4 or more). In each entry there is a number representing the rating of said account. These numbers differ within the duplicates. Example............
I am trying to find a formula that will choose a "winner" and enter a "W" based on the highest rating within the duplicate entries and then ideally assign an "L" to the loser duplicate account(s). Sometimes there is no rating, other times the rating could be the same in which case any account (does not matter) can be assigned a "W" and the others an "L"
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.
I have two columns (let's say column A and C), i would like to add the values in column A only if they are higher than the ones in Column C in the same row... how could i do that?
The sheet looks something like this, i would like to add the values in the first column only if they are higher than the ones in the third column and then of course get the sum of them... i would also like to know how many were there in total, but i guess i could do that with a count formula i just need the parameters
$ 67,100.00 $ 110,000.00 $ 165,100.00 $ 146,034.00 $ 239,400.00 $ 220,100.00 $ 90,184.69 $ 147,843.75 $ 168,500.00 $ 51,011.25 $ 83,625.00 $ 95,100.00 $ 89,860.32 $ 147,312.00 $ 198,200.00 $ 159,121.36 $ 260,854.69 $ 26,000.00 $ 129,941.44 $ 213,018.75 $ 235,700.00 $ 88,869.38 $ 145,687.50 $ 95,600.00 $ 27,772.54 $ 45,528.75 $ 43,000.00
I have a reference value(say 'X') at L3, now i want to select a value next higher to this value(ie..'X') in the column ''C'' say and also i have to print the corresponding values of the row corresponding to B,C,D,E,F etc.
View 1 Replies View RelatedObjective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #Lap TimeThreshold2 or more laps
0118.285118.2
192.7492.74
284.82
387.587.5
486.586.5
582
681
784.2
887
981
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above, I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
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.
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 RelatedIn 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.
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.
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
I have a list with all our company cars (300). I also have a list with all the transactions of the fuel cards (12000 lines). Now I need the find for every company car (exact match) the nearest fuel-consumption-date (closest match to the given starting date) and take the mileage of that transaction. I've added an example. I have tried linking carnr and date (like carnr&"#"&date) and match these, but I don't always get the right car number.
View 1 Replies View RelatedI cells B5:B15 I have a list of dates from last year. I need a formula that I can put in cell D5 that gives me the date that is closest to the same day last year.
Example:
If today is 4/9/14
and
From B5:B15 there were the following dates:
3/12/13
3/19/13
3/29/13
4/5/13
4/8/13
4/12/13
5/6/13
5/29/13
Cell D5 would contain
4/8/13 since it is the date closet to same day last year.
I am looking for a formula to return the expected row/location in range B2:B13 as shown in col. F
Col. B dates are and cannot be sorted
The idea is to find the exact or the closest lower date to those in col. E
as per two criterias (col. D+E)
I prefer no helper column and no macro
(see attached WB)
I am wondering if it is possible to search a column full of assorted values which is unsorted, and then return the closest value.
I have written a previous thread which was similar, but misleading, i feel this thread is more to the point.
Currently i use this code, but it only finds the closest highest value if the data is sorted in ascending order.
I needed to know how to find the closest match to a reference number
regardless of whether its larger or smaller. I did a search and found a post
back in March that said to use the following:
=SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list)
I applied it to my application and it works, I just have no idea why. Can
anyone explain this formula to me or tell me where I can find a good
resource.
I have a database where I have groups defined. For example, Parent 0001 is called Bag1. It contains an apple, orange, grape, and a pear. Parent 0002 is called Bag2, containing an apple, orange, and a peach. So on and so on...
Below is an illustration of what I explained above:
Parent
Level
Description 0001
1
Bag1 0001
2
apple 0001
2
orange 0001
[code]....
I need a way to input items that are in the groups and find the groups have all of the items or the group with the best match. I need to know if the match is 100% or if it is only a partial match.
The bag might have 2 items or it could have 20 items and they can be in any order.
I have modified some code I found in these forums that once I enter a value in a cell I can have it find the closest match to that value and scroll down the list and paste that value as an offset. The code works perfectly, but my data will often contain the same values repeated and these aren't duplicates and I need to know how to loop the code to ensure it doesn't just find the first value but also the second (if the first is already populated) Code is:
Sub findclose()
Dim rng As Range, Dn As Range, Mx As Single, oAd As String
Dim num As Range
Set num = ActiveSheet.Range("B1")
[Code] .......
---- What I need to do is insert a loop of some sort that basically says IF my offset cell has a value then to find the next closest amount and continue the macro.
So if my list was:
700
50
500
600
500
And I wanted to find 499 then my list would look like:
700
50
500 499
600
500
BUT if I wanted to match off another amount of 501 it would keep the 499 it found and then find the 501 making the completed list look like:
700
50
500 499
600
500 501
Right now the amounts are being overidden as I don't know how to loop it...
I have a sheet of pump test results, placed in rows, where the test rig is set at a specific speeds and flow and it records pressure. At each stage of the test a number of snapshots will be taken (Usually between 3-5 at each stage), so I have 3-5 rows of very similar data. I only need to use the best row from the selection available, so I am hoping to find a formula or code (Don't care which) that can look at the results and select the row of readings where the speed AND the flow are closest to the targetted data.
For example: In cells G6:G8 I have flow result values 0.129, 0.151, 0.156 and in H6:H8 I have speed result values 72.536, 71.82, 72.13. These are actuals. In cell N6 I have the target speed (75) and in O6 I have flow target (0.15), but these could be moved anywhere to suit really. What I was hoping to do, but am struggling big time, was to just flag up the row of readings where these two values are closest to the targets by placing a check mark in column L:L which I have formatted in monotype sorts and would like to place a "4" (Which gives me a check mark) on the 'best' row. From that I can do more with some simple IF statements.
Is this feasible, or am I trying to do something beyond Excels capability, it is certainly beyond mine at the moment. I was looking at the check mark method, but that is not essential, so if anyone has any suggestions on an alternative means of identifying the best row of data, that might make it easier to do, then I am listening.
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
I'm looking for a way to split a cell with text into two cells. The first cell can only contain up to 40 characters, the rest needs to go in the second cell. However, I don't want the text to be split in the middle of a word. So basically, if the text is in cell A1, I need to find the closest space to the left of position 40 in A1, and move anything to the right of this position to B1. Example:
Original text:
A1: One green apple and a bucket of small onions that smell nice
I want to avoid this:
A1: One green apple and a bucket of small on
B1: ions that smell nice
I want to achieve this:
A1: One green apple and a bucket of small
B1: onions that smell nice
I am really struggling with the following formula:
MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)
I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.
I have Excel 2010 & Window 7. I have 2 sheets of business names, some businesses in sheet 1 may be named slightly different in sheet 2 and not all businesses in sheet 1 are included in sheet 2. I would like to be able to find the closest match (from sheet 2) for the businesses on sheet 1 and return the closest matched business name.
View 2 Replies View RelatedI have two sets of data:
Data set #1
Indicator PriceIndicator Price Date
2.1 10/27/08
2.11 10/22/08
2.17 11/21/08
2.38 03/20/09
2.38 03/25/09
2.46 03/19/09
2.5 03/09/09
2.5 03/24/09
2.53 12/04/08
2.73 12/09/08
2.82 12/24/08
2.83 12/18/08
2.89 12/12/08
2.9 03/13/09
Data set #2:
Close Price Close Price Date s1s2s3s4r1r2r3r4
2.25 12/11/09
2.30 12/12/09
2.40 12/13/09
2.00 12/14/09
2.12 12/15/09
2.50 12/16/09
2.51 12/17/09
2.53 12/18/09
2.49 12/19/09
What I'm attempting to do is find what are the CLOSEST four indicator prices in data set #1 are above (r1, r2, r3, r4) and below (s1, s2, s3, s4) the closing price in data set #2. Also, I can only use the indicator numbers in data set #1 that are on or before the close price date in data set #2. Because of this, not all of the r's and s's will be filled in.
I would like to use just regular excel formulas, but I have a feeling that VBA may be my only option.
I have the following table of stocks with corproate action types and dates with a ticker / identifier per stocA1:
Type
B1:
ID
C1:
Date
[Code]....
I am trying to find the NEXT Dividendcorporate action that is CLOSEST to the current date. I've done some google trawling and found a few formulae that seem to work only if the identifiers or dates are in a particular order. ALso having trouble using TWO criteria
I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.
The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.
I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:
1) The user inputs the starting cell. In the Sample I use G200.
2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.
If the value is greater than original cell then there are 2 possible outcomes:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.
Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.
OR
Here's the logic now in reverse I need:
If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.
Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.
There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.
I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.
Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.
The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.
Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.
EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.
I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.
After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works! However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set.
File attached with sample data sets.
I have an excel workbook with 2 worksheets. One worksheet shows the MASTER LIST of COMPLETE Customer Names (e.g. ABB Supplies Incorporated). The other worksheet has information on customers but the customer names typed in are incomplete (e.g. ABC Supplies). I need a macro that would look do a comparison of the customer names in the 2nd worksheet to the Master List worksheet and pull the data (complete name, address, etc.) for those that would match (partial match since company name is 2nd worksheet is usually incomplete).
View 2 Replies View Related
