Finding Lowest Value And Displaying Offset
Jul 20, 2012
I have a spreadsheet where I want to do some fancy stuff:
1) find the lowest value in a range of cells (S9:S17)
2) output its corresponding value in A9:A17 into another cell, say C87
3) increment its corresponding value in C9:C17 by 1
4) repeat 9 times, but inputting it to C88, then C89, and so on.
Everything in the spreadsheet depends on the values in C9:C17, including the range S9:S17, so when the values in column C change, so do the ones in column S.
But I don't want to actually change anything, just simulate it. But because the values in S9:S17 are formulas from yet another column in the spreadsheet, the best thing to do would be to remember the column C values beforehand, do the 4 steps above, and write them back afterwards, otherwise the macro starts to get complicated and brings in more values etc.
View 2 Replies
ADVERTISEMENT
Feb 3, 2014
question 1 : In the below sheet, is there a way to use the condition highlighting the quantity for each item which has the lowest (prices/pcs) value?
for example : comparing the ITEM 1 -> $8/10 , $6/8, $9/20, $11/20
and highlight the 8pcs for item 1, indicates item 1, seller bill has the best offer.
Question 2 : Since I will have at least 50 items, is there a way to just copy the formula and apple to each row ?
question 3 : I would also like to apply a formula that can calculate the price and quantity of each item. for example : on the I column, for Item 1, i would love to order 3 orders, since bill has the best offer, so I will have 24pcs on column I3. And i would love to apply such formula to each item for each row.
Question 4 : For the cell J3, I would like to know if bill has the best offer for the item 1, order 3 orders, the price for item 1 will be $18. And I would like to apply such formula to each row for each item.
I do not want to create another column for each seller: such as ($/pcs) to find out the best offer, since i will have at least 35suppliers , and try to make the sheet look as nest as possible.
View 1 Replies
View Related
Jul 24, 2014
I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?
View 3 Replies
View Related
Oct 24, 2007
I need to find the lowest price, mark it up and designate which vendor it’s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new “price” and “vendor” columns and then I can go through and mark it up.
Here’s a small sample of what the sheet looks like:
Item #Desc V1 V1 CostV2V2 CostV3V3 Cost
123456brush Advance 2.56Weiler5.65Osborn3.25
View 2 Replies
View Related
Aug 30, 2013
I had the following:
0.000%
-2.140%
-1.729%
0.973%
1.206%
1.469%
[code]...
I want to know the lowest number from the peak high to the lowest low was? If I have not explained that properly, if I had 12.00% as the highest and the next few rows consistently, and gradually moved to a low of 8.00% that would be a difference of -4.00%. So -4.00% would be my answer. formula as I need to know this one number in a column of 4000+ percentages like you see above.
View 9 Replies
View Related
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.
View 8 Replies
View Related
Feb 16, 2007
I am trying to search for a string of numbers (column 2) in an array, and have "YES" be written on the same line in column 3 if the string is found in the names ANYWHERE in column 1. Please see the desired results on the picture in column 3.
I have tried many things, including SEARCH function which can only work with 1 cell not many, COUNTIF and more advanced functions, but I think have not succeeded because of my lack of knowledge in arrays.
View 9 Replies
View Related
Nov 15, 2009
At my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day.
What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like
View 2 Replies
View Related
Oct 6, 2008
Attached is a sample file that has a Data entry sheet in which the user can paste in data. They can test & paste anywhere from 1 to 50 compounds at a time and run the list through 1 to 55 Assays. I want to generate a sheet (Plate & Assay Info) that would first display the list of compounds that were tested as well as the list of Assays that they were run through.
View 13 Replies
View Related
Jul 19, 2013
want a way to find out any special character in text file (.txt).
The .txt file I use is very large about 100 mb to 1GB. I need to find a way to write vba code that asks for input text file and the validates it and gives the message that following special characters are present in the file. Also, it gives their column number and row no's, where they are located.
The characters which needs not treated as special characters are numbers (0-9), alpha (A-Z) and special characters (@,-,%,$,+,=).
View 2 Replies
View Related
Mar 23, 2012
I have a large file where I want to name about 30 ranges. Initially I had done this using the range addresses and it worked well, but I continue to modify the spreadsheet and add and delete rows, so I need to go back into the code and change the ranges manually. I know there has to be a better way.
What I wanted to try doing was finding some specific text. That text (in all the ranges) is actually one column to the left and 33 rows below where I want to start the named range. Additionally the named range will be a 10 x 33 range. For example, if the data starts in B1, the data to be named will be in B1:K33 and the search title will actually be in A34. Hopefully that's clear...
My original code looked like this:
Code:
ActiveWorkbook.Names.Add Name:="[MyRangeName]", RefersTo:=ActiveSheet.Range("B2:K33")
I was attempting to us the Find function with this code, but I can't seem to figure out how to tell it to look for the text (that would be in A34) and then name the range that would be offset by (-33,1) and then name the entire range.
View 3 Replies
View Related
May 31, 2014
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
View 3 Replies
View Related
Nov 1, 2006
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
View 9 Replies
View Related
Apr 21, 2007
Is It Possible to use VBA Coding to Display An Image (Object),
And Un-Display or Remove that Same Image (Object) using a VBA Code?
Example:
I have a Mailbox Picture that I would Like to Show, but only when a cell's
value = 5. If the Cell's Value is not 5, then don't display the mailbox.
View 12 Replies
View Related
Oct 26, 2008
I have attached the .xls file.
The basic setup is as follows:
(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)
(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.
For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc
As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.
(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.
NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.
OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.
I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.
HOWEVER, what i WANT is the output as shown in columns Y-AF.
So as you will notice, there are 2 problems:
(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.
How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?
(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.
Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.
View 7 Replies
View Related
Mar 22, 2013
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria)
Search range is: B1:D400
(for example)
Cell B40 = samuel
C40 = Driver
D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1()
Range("B1:D400").Select
Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 2 Replies
View Related
Feb 25, 2014
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333
2 Sam 22
3 Sam 22
4 John 5
5 Sam 22
6 Sam 1
7 Tom 3
8 Tom 333
So the answer would be
Tom = 2
Sam = 2
John = 1
View 8 Replies
View Related
Aug 4, 2009
I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.
I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?
View 12 Replies
View Related
Dec 12, 2011
I need to search across 15 columns and return the lowest value in that row? How to do this?
View 8 Replies
View Related
Apr 16, 2007
I have a long list with names of banks. I also have three different numbers (between 1 and 10) for every bank. Eg Bank A 5 7 4, Bank B 5 9 3. Sometimes there are not three numbers for a bank but only one or two. There are never more than three numbers though. I want to have a forth column. This column shall give me a new number based on the other numbers. The sorting shall work like this. If a bank only has one number then that number shall be presented in the forth coulmn. If there are two numbers then the lowest one shall be used. If there are three numbers then the two highest numbers shall be chosen and if they are different the lowest one of the two highest shall be presented in the forth coumn. I do not know if you can do this using normal worksheet function of if a user defined function is necessary.
View 9 Replies
View Related
Nov 15, 2007
I am trying to create a formula that will show the lowest of three separate cells. I don't know how to do the Vb script,
This is what I need:
Cell H34 HOURLY RATE (=$920)
Cell I34 DAILY RATE (=$980)
Cell J34 MONTHLY RATE (=$1020)
Cell K34 displays the text "HOURLY" since it's the lowest of those 3 options
View 9 Replies
View Related
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
Jun 23, 2014
I have 4 TextBox on a UserForm. First three to enter value and the last one to return the lowest value entered in to the first Three TextBox. How to determine the lowest value and show it on last TextBox.
View 2 Replies
View Related
Dec 21, 2013
Calculate Lowest Value By The Length
View 5 Replies
View Related
Nov 17, 2008
I'm making a work sheet for items and would like to have the lowest price in a column automatically flagged by bolding, color change, font change or something.
View 14 Replies
View Related
May 7, 2009
What is the formula I would need to use in the attached spreadsheet to calculated the lowest or first occurrence within a row? As you can see in the top table Product A is delivered in weeks 2, 4 and 6, what I need is the lower table to show the first week i.e. wk 2 that the product has been delivered.
View 2 Replies
View Related
Aug 31, 2009
I am new to excel and have to create a comparison chart that shows the lowest cell result in each row ie
23 25 28 13 42 16 13
21 34 25 17 21 23 17
I will have 36 rows by 20 columns I also need the lowest in each row to appear at the end of the row in the total column. I would like to highlight the cell by a cell colour rather than text colour.
View 9 Replies
View Related
Sep 24, 2009
Could any of you Excel bods please help me find the correct formula to enter in order to calculate the following reasonably simple sum:
3 cells with numbers, say, 1, 3 & 7.
I simply need to get my worksheet to look at all three cells and then calculate the result of adding the biggest and smallest number together. i.e. 8 in the example given.
View 8 Replies
View Related
Sep 30, 2009
Is it possible to compare the number in one column with multiple other columns (same row) and return the lowest of all to a cell?
View 3 Replies
View Related
Feb 8, 2010
i want to underline and bold the lowest value in a column.
this i thought was easy enough to do using conditional formatting.
my conditional formatting at the moment makes the cell which meet a certain figure go green and red if the figure dosent meet the target figure.
i cant do this as even if the cells dont meet the target figure i still want the lowest number underlined/bolded, and i cant seem to do this without making the colors go wrong
View 10 Replies
View Related
