The content of Cell A1 looks like this attccggttaattcccccaaaattt
(only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
I need to find a formula that will find letters in a referance, for example i have referances like - MNE DJM & ZZPAR i need to find a formula that will find me the ZZPAR looking for "ZZ" i then want this to tell me what tpYe of referance number it is and put this into column Z.
Normal referance number like DJM and MNE i want this to show as "BROMLEY"
aND ZZPAR as "Chester"
I have tried something like the following but this is not working
In column A I have a set of Ids that are not unique. In column B I have a set of latlong values for those Ids in column A. In column D I have the unique list of Ids from column A(Removed duplicates from column A & pasted them in column D). In column E, I need the latlong value which has highest frequency for each Id. I tried countif,Frequency and tried mixing them up with various other formulas but it is out of my reach.
View 3 Replies View RelatedI need to limit the number of letters that a person can write in a given cell.
View 5 Replies View RelatedLook in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies View RelatedI have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
I wanted to know if there was a way to find the Maximum value given a number of conditions are satisfied.
Similar to COUNTIFS and AVERAGEIFS, where you state the data range to use, the condition's range, the condition, then the next condition's range, the next condition, etc....
I can use find and replace to remove the following, however as I would be doing the same process numerous times it would be very tedious.
I would like 2 VBA scripts.
1 - replace the following letters (uppercase & lower) in Column A and C replace A, V, N, Z, X, Q
so if it finds A it would remove A from the cell.
The cells may contain other letters including these one's i.e
A123XCB235 or
123VBN7779A
2 - The second independent code would replace the characters replace A, V, N, Z, X, Q with P, M, L, R, J, H ie. A becomes P, V becomes M etc...
Again the cells may contain other letters including these one's i.e
A123XCB235 or
123VBN7779A
i have colums of data containing random figures containing letters and numbers.
I want to completely remove the letters from the colums, leaving me with just a numerical figure. Is there a way to do this? There is no set pattern to work with as there could be 1 cell with 2 numbers and 6 letters, and the next cell may have 6 numbers and 1 letter.
EG: COLUMN A
aaa567783a
3782dc
23388fjjas2
22dd
I want this to simply be
EG: COLUMN A
567783
3782
233882
22
a macro finding the max number in col B in each day (hr 0- hr 23) and in col C put a "1" next to it.
View 10 Replies View Related01-Oct-12
02-Oct-12
03-Oct-12
04-Oct-12
05-Oct-12
10
212
23
24
58
Here the maximum value is 212, Is there any way to find its corresponding date. Date in Row(A1:A5), Values in Row(B1:B5). Tried my best to get the date; but no way.
I want to know how to get the max of text.
I am having sheet which is showing Codes for deliverables as A,B,C,D as an entry.
I want to pick the latest code, means i want search the row for Max value, i.e is D and display it in cell.
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions.
So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value).
A small sample of what the data set looks like:
2008 Q22008 Q32008 Q42009 Q12009 Q22009 Q3
Atlanta771773768761749756
Austin770786784779776776
Baltimore947948949950950953
Boston1,6471,6601,6541,6481,6301,613
Chicago998998996985984986
In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, cell A4 is PM-B00, etc. The first two positions are always alphabetic. I want to sum all the numbers in column B whose adjacent column A text starts with "PM".
I tried =IF(match("PM*",A2:A100,0),b2,"") but just get "NA"
Log sheet Col a = Dates
Col d = Route ( Letters and numbers)
Col F = Times (1.2-2.3- examples)
Log sheet has 1,550 rows +
How I have been finding total for the Last 12 months is the formula below
=SUMIF(LOG!A:A,">="&TODAY()-365,LOG!F:F)
Now what I would like to try and do is the same BUT.
using COL D (on LOG sheet) to find out when I went to for example "JTF"
be advised that COL D is a route, so there are many results in each cell in col d..
So examples below
COL F On Log sheet
OERT-JPF-JMF-ASG1-JTF
OERT-JTF-JTF-ADC17-ADC17-ARAB3-ARAB3-DHAMC-JSK
OERT-JTF-JTF-ASG1-R655-JPF-JMF-LCL-JTF
OERT-JTF-JTF-R655-DHAMC-HAW-DHMAC
OERT-JTF-JTF-JTF-HI4-HI4-R655-ADC38-RS55-ES76-JPF-JMF-JPF-JMF
OERT-JTF-JTF-HI4-R655-ADC38-ES76-PMT-FLIGHT TOUR VIPS
I am looking for a formula that will return the lowest value from a five cell range using letters instead of numbers. If the 5 cell range is empty the cell will remain blank. Not all the 5 cells may be used - it could be anywhere from 1 to all cells.The weightings of the letters in terms of their numerical value are as follows:
F=0
P=1
M=2
D=3
Examples of desired results:
From A1 to A5 the following letters are inputted: P M M D P. Result in A6 = P as P is the lowest numerically in the above list. B1 to B3 = D D M. Result in B4 = M. C1 = F. Result in C2 = F. All cells blank from D1 to D5 = cell in D6 remains blank.
I would like to run a macro loop to find Maximum value out of these three column and return text into a cell as "maximum value found = A" or B ....
View 12 Replies View RelatedI'd like to have the cell address returned along with a value when I use the
MAX function.
It is any code in VBA to find a Maximum value whit 3 conditions from one sheet to another ? Somethig like this:
ActiveCell.FormulaR3C7 = "=SUMPRODUCT(Max((sheet1!R2C6:R65536C6=RC[-1])*(sheet1!R2C3:R65536C3=RC[-2])*(sheet1!R2C4:R65536C4=RC[-3]))"
ActiveCell = ActiveCell.Value
I need to find a max value from a sheet if acomplish 3 conditions in another sheet.
I am looking for a way of finding the maximum value in column H for each row in a pre- filtered range using VBA.
For example, the result of of filtermacro1 may return several records derived from a database of several hundred records. In the example below, the name of the person is in column C, followed by their H column value.
Audrey Perkins, .5
Marc Bloomberg, 2.0
Matt Phillips, .5
Paul Pachson, 2.0
However, the H column value may not be the maximum value for the rows above, as there are other records for Audrey, Marc, Matt & Paul in the database.
If the names are matched to the max value in column H for each record it should read
Audrey Perkins, 1.0
Marc Bloomberg, 3.5
Matt Phillips, .5
Paul Pachson, 6.5
How would I query only the names in a pre-filtered range and obtain the highest value in column H for each ? These values then need to be copied to H7 in a sheet called Detention Register.
formula to do the following:
Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.
So in a 5 cell range the values could be:
blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).
I have a database to which I am connecting a form, I need to have a unique ID for each record and due to the number of tables need to include letters in my numbering (ex. U00001, for Users; B00001, for Books; and V00001, for videos.) I have adjusted a very helpful macro I recieved from Roy Cox and am currently trying this code on the "user form":
Count_Row = 1
DATABASE_RECORDS = Sheets("Users"). Range("B1:B10000")
'To identify the next blank row in the database sheet
For Each DBRECORD In DATABASE_RECORDS
If DBRECORD <> "" Then Count_Row = Count_Row + 1
RowNum = Count_Row
X = RowNum - 1
Sheets("Users").Range("A1" & RowNum) = "U000" & X
Next DBRECORD
This is supposed to find the fill the "A" (ID) column after the "B" Column has been filled. Currently It is placing U0001 in cell A12 when all that is in the sheet is headers.
I spent couple of hours trying to figure this out, but without success. Please check the attached example. I need a formula that will find the max value (number of views in English anguage) in the column "I" and display the corresponding "Headline" value in the column "B". In the example it is "Headline 5".
I can not change position of the columns, i.e. "B" must remain "B" and "I" must remain "I".
I experimented with Index and vlookup but to no success. I have outlook 2003.
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View 4 Replies View Relatedin which the OP was trying to find the maximum date (Col B) in which the value in Col A occurred, DonkeyOte used this formula as a possible solution
View 4 Replies View RelatedI've an excelsheet with quite advanced formulas that area really nested and using quite a bit of iteration.
What I would like to do is finding the maximum of
A while Amin<A<Amax
By changing B
There is a Variable C than should be constant unless it becomes larger than Cmax ( Cmax dependas on A,B ,C, D and all kinds of things)
If C is > than Cmax there is a variable D that can be changed has to be less or equal to Dmax
So the question is how to find the maximum of A while fulfilling all these variables?
What I've come up with so far is:
I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..
For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.
In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..
How do I find the minimum and maximum numbers in a table if I do not know the size of the table but can assume the user will choose the first cell (upper left cell) of the table and run the macro?
View 5 Replies View RelatedI have a column of values the values can be "T", 0.00 ->any number to the nearest hundredth. I thought I had this problems solved a while back but after having time to do some quality control I found that anytime there is a value of "T" the macro does not work. I have had some great help with this in the past to get as far as I have but I still cannot seems to get it 100%. I have attached a sample file. The result that I would like to see is in the cell BH145 is "16,17,23" without the quotes of course.
When you open the worksheet you will see that the values of concern are precipitation values. These values are rounded to nearest hundredth. When there is a trace of precip however it is recorded as "T" therein lies the problem that I cannot seem to solve.