Find Lowest Value In A Range Of Letters

Apr 30, 2007

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.

View 3 Replies

VBA Find Function - Search Through Selected Range Of Cells For Key Letters

Jan 6, 2014

I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.

Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

[Code] ........

View 9 Replies View Related

Find The Lowest Value Of One Number?

Sep 2, 2012

i have a table with multiple codes and quantities along with other info in it. What i need is to take each MATERIAL CODE and its lowest quanity and transfer it to another cell. I have tried using DMIN but can not quite get it. It stopped working after the 6th one.

103057300 -800
103057300 -700
103057300 -250
101789050 50
101789050 -70
101789050 -90

So i want to to take the following quanities that are in this font and put them into a separate cell.

View 1 Replies View Related

Find The Lowest Values ...

Jan 19, 2007

i have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2 i want to do this with every row from row 2 to 55

View 9 Replies View Related

Find Lowest Value In Column

Mar 1, 2007

if it is possiable to do a find function to find the lowest value in a colum and then output that entire row. e.g a list of dates, I need to know what is the oldest date and what row that is for reference.

View 9 Replies View Related

Find Number Or Next Lowest

Aug 1, 2008

I have three columns with 1 number in each row. I'm trying to find a number, and if that number does not exist in the 3 columns I would like to find the next smaller number. The numbers have up to 4 decimal places. i.e. 16140.0311.

So for example if a user searches for 15950.012, and that does not exist but 15950.009 does with no numbers in between then the answer returned would be 15950.009. Auto Merged Post Until 24 Hrs Passes;I should probably mention that I would like to insert a new line with the number originally searched for, after the number found.

i.e. search for 15950.012. Not found. 15950.009 next lowest. Insert new line after 15950.009.

View 9 Replies View Related

Find Hightest/lowest Frequency's

Nov 20, 2008

I trial tested many another forumla's before posting this.
I'm having a hard time building this simple function see image:
[url]

The formula I need finds the hightest & lowest frequency appears of a number from the index list (index numbers range from 0-9 or if needed changed to 1-50). Along with the hightest/lowest frequency's it needs to also find the second hightest/lowest frequency's i.e.

need result: (9 2) (0 3)
(0 & 3 did not appear so there listed as the lowest frequency's)

index
2 <
2 <
9 <
4
5
6
7
8
9 <
8
7
6
9 <
4
...ect

View 14 Replies View Related

Find Lowest Value From Multiple Sheets

Jul 13, 2009

Im creating a list of cash and carry places to buy drinks but im so clueless on how to go about doing it.

Heres the situation:

In sheet 1 I have a list of Drinks and the prices the shops are selling it for.
I have duplicates of the drinks so say for bacardi i would have one row with one shop with its price and another shop with another price. I cant put it into columns because there is other information such as the quantity the shop sells in one box etc.

On another sheet (sheet2) i have a kind of shopping list. This has all the drinks listed in Sheet 1 as mentioned above but NO DUPLICATES. What i need it to do is find the drink is Sheet 1 and pick the row with the lowest price from the multiple entries and copy that price and shop name over to sheet2.

I hope this is easy to understand. Please let me know if I need to explain some bits again. Im trying my best to figure out how to do this. I have no clue how to do it in Access.

View 14 Replies View Related

Find Difference Between Lowest Two Numbers?

Feb 15, 2014

I know this is probably really easy to do but I am new with excel. I have a list of 8 numers in 8 cells and was able to find the lowest. But now I need to take the list and subtract the lowest number from the second lowest and have the difference dispay in the target cell.

View 8 Replies View Related

Formula To Find The Highest And Lowest Value

Dec 5, 2006

Is there a formula I can use to find the Highest and Lowest value in a column?

View 9 Replies View Related

Find The Lowest Date For Duplicate Id Nos.

Jan 25, 2007

I am attaching a small sample data set. The first column contains the ID numbers and the second column has the dates. The rest of the columns are some data. When you look at the ID numbers, there will be some common ID Nos. numbers, for example, 300003 (4 in number) but the dates are different for them. What I am going to do is to create a pivot table with the ID Nos and the months. But I want the date to be only the oldest date for example, I want the date for 300003 to be 12/3/2004.

View 6 Replies View Related

Find Lowest Percentage In A Series Of Numbers

May 27, 2014

I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...

Row 2: 12, 100, 43, 1444, 141, 332
Row 5: 25, 140, 55, 3000, 244, 440

Which number in row 2, is the lowest percentage of the total possible points found in row 5?

B2 is 45% (12 divided by 25), C2 is 71%, D2 is 78%, E2 is 48%, F2 is 58%, G2 is 75%.

I want a formula to tell me in a new cell that "12" (in B2) is the lowest percentage in that series.

View 4 Replies View Related

How To Find Top 10 / Lowest 10 Values Based On Month

Jun 18, 2014

I have 4 grids on the trending tab. I want to find the top 10 highest Color assets from Fleet Volume -Color tab under the correct month.

So for example, if the month is May as in cell G3 on the trending tab, then look at the data under the month of May on the Fleet Volume-Color tab and find the top 10 assets and drop in the City, Address, Model, Serial Number and then volume to the 1st grid on the trending tab, then repeat for Highest B&W

I want to the do the same for each of the other 3 grids on this tab. I want this to update based on the month on both tabs.

Of course, the data on the Fleet Volume tabs is a small range due to size, the data is a lot larger.

Is there are way to do this with a formula? I tried Large and small formula but not too sure how to bring over the other data like City, Model etc.

View 4 Replies View Related

Using Index / Match / Mini To Find Three Lowest Values In A Row

Apr 22, 2014

I've used the below formula to fill column D with the name of the cheapest supplier for the parts listed in each row.

=INDEX($G$1:$M$1,MATCH(MIN(G3:M3),$G3:$M3,0))

Is there a way to fill columns E & F with the 2nd & 3rd cheapest suppliers?

My table is shown below:

Capture.PNG

View 2 Replies View Related

Column Title Lookup: Find The Lowest Cost

Sep 24, 2009

I have been assigned a task of finding the lowest cost of four possible solutions however I have quite an extensive list of items to work with. To make this easier, I need to be able to find the lowest cost in my row (which is not sorted by lowest to highest value) and return the column heading associated with that lowest cost.

View 2 Replies View Related

Find Four Highest And Lowest Numbers (sorted By Date)

Jul 6, 2013

I have two sheets:

Sheet one has dates, my prices on each of those days, and the fields h1 h2 h3 h4 (h1 is the closest higher number, h2 is the next closet higher number, etc.) and l1 l2 l3 l4 (l1 is the closest lower number, etc. etc.)

Sheet two has certain dates and prices of a competitor's prices.

What I'm trying to do is two things (see attached):
1) find the four closest higher and four closest lower prices (if available)
2) use only the prices that are on or before the date in question.

I've tried combinations like LARGE and OFFSET and so far it is not getting me anywhere.

Can this be done???

View 4 Replies View Related

Formula To Find Lowest Unique Number In Series

Sep 22, 2009

I'm holding a Reverse Auction where people pay a dollar to place a bid, BUT the twist is they are giving me a number that they how will be the lowest number but it has to be the only occurrence of that number (greater than 0).

During the party people can guess as much as they want to pay. I think i'll use a spreadsheet with their names in column A and go out in the row with however many cells for how many numbers they guess (so there would be blank cells in the overall range of the whole list if one guy buys 10 numbers and another only 1, for example).

So, i need a cell at the bottom that tells me the lowest number that wasn't guessed more than one time.

I've found how to FIND duplicates and the lowest number but i don't know how to write it so that it discards the duplicates.

View 9 Replies View Related

Find And Remove Letters?

Dec 5, 2011

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

View 9 Replies View Related

Find And Replace Letters

Apr 24, 2008

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

View 9 Replies View Related

Lowest Value In Range Of Data

Jul 2, 2008

I am trying to place a formula in Cell "S5" that will look at sheet "audusd" in the entire Column D and return the Highest value.

View 9 Replies View Related

Find Letters In A Reference Number

May 6, 2009

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

View 5 Replies View Related

Find All Cells In Col A That Start With A Given 2 Letters

Nov 7, 2002

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"

View 9 Replies View Related

Can I Find Cells That Contain LETTERS During The Last 12 Months

Dec 19, 2007

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

View 9 Replies View Related

Find Maximum Number Of Letters

May 27, 2009

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.

View 9 Replies View Related

Sort And Obtain The Lowest Value In A Range

May 1, 2014

Excelforum.jpg

What I want to do is in column 5 row 1, I want it to show a value only if all 4 columns, row 1 are filled in. For example in the picture I want to show a blank in the first 8 rows but to start showing a value on the rest of the rows, in this example IPE 240 to be the first value. When it shows this value from this row I want it to obtain the value with the highest percentege inside. For example in this picture the correct value first value to be shown in column 5 will be IPE (84%)

Ive done this code so far: =IF(AND(B256<>"",D256<>"",F256<>"",H256<>""),Here I want it to show the value on the row that has the highest percentege,"")

View 14 Replies View Related

Nth Lowest Number In Non-Contiguous Range

Aug 10, 2007

I want to use the Small function on a set of cells that are non-contiguous. But I know that the Small function only works on an array, which cannot contain cell references. Copying the cells to a contiguous range is not an option, as some of the cells I want referenced are generated on the fly in a Sumproduct function. Is there some alternative I could use? I can use the Min function to get the lowest value, as it allows for references, and the Max function to get the greatest, but what about those in between?

View 4 Replies View Related

Allocate Numbers To Letters And Find Average

May 7, 2009

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).

View 9 Replies View Related

Show Highest And Lowest Figure In A Range

Oct 13, 2008

how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003.

View 5 Replies View Related

Finding Highest And Lowest Values In Dynamic Range?

Dec 28, 2011

I have a column that updates automatically by placing a value in in the next blank cell in the column. I am trying to write two formulas to identify the highest and lowest values in the preceding 10 cells of each update in the column but seem to be stuck in MIN/MAX/OFFSET hell.