I'd like to use the SMALL() function in excel to pull out the second lowest unique value in a list, but I'm not sure there is a way to do this. For example, if the array is {1,1,3,10,2,6}, then SMALL(array,2) returns 1, but I'd like it to return 2. Is there a way I can modify this function or use a different one to achieve what I want?
View 4 Replies
I have the G column listed with number from 1-100. On the B column I have the corresponde titles for each number in G.
What I want to do is =Look at the G(1:100) take the top 10 lowest value and write the correspondant title nearby.
I think it s possible with Min or Small functions, but I dont know how to, especially taking the title names nearby.
Criteria:
U4= 12/01/2013
Data:
(A) (B) (C)
01/01/2013 02/01/2013 12/01/2013
1 3 4
2 2 1
4 1 1
Result:
Small - looking for smallest to fifth smallest (separate cells B5=1, B6=2 etc.)
Suppose you have a dates mix in column A with year-month-day format, for example:
ABC12012-04-27Call to the Doctor at 9am
22012-05-13Chicago Concert
32012-04-14Buy ticket to theater
42012-05-03Take children to match
52012-04-07George birthday
62012-04-21Bring the car to inspection
7
And in Column C you want to get the first or the third date of a month, for example for April
Please see the attached worksheet for details. I would like the array function to search for instances of the word "FALSE" in column E and return the values of columns A:D when a match is found. I have done this successfully when the lookup value is a value in the first column of the range, but cannot seem to do so when the lookup value is in the last column of the range. I have received a #NUM! error each time.
View 9 Replies View RelatedI compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
IF(ISERROR(INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2)),"",INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2))
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
I have a column of number that I want to use the Small & Large function on, skipping duplicates.
Example:
Column A has the following numbers;
1
6
1
4
3
2
4
6
17
8
21
8
10
13
I want the Small function to identify the first lowest number (1) and then the second lowest number (2), not (1) again.
I have a Big Question about the SMALL Function in Excel. I would like to know if it is possible to use a syntax that will make the small function update my graph dynamically.
Personnel transfer out of the Department on know dates... but they come in to the Department all the time. I would like to know if I can increase the "range of my data" even if I don't have data in the cells (i.e. B2:B6 have data, I want the range to include B7:B16, which has no data)... When I have no data in the cells I get errors/ circular references...
Ideally, I would like be able to add or remove personnel and the SMALL function would "organize/update" my graph dynamically. Is this possible?
is there any way of using the =SMALL function to rank only numbers above zero so that the zeros don't keep showing up as the smallest figures?
View 2 Replies View RelatedI have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.
View 3 Replies View RelatedI'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
View 4 Replies View RelatedVery slow small vba code. I have the following
View 2 Replies View RelatedI'm creating a report that pulls certain record entries from another worksheet (Activity) in the same book. Originally, it only pulled one type of activity. "SO". Now I need it to pull "SO", "CC" and "BT" type activities into the same report. Currently my formula looks like this and works fine for only looking for the "SO" type:
View 2 Replies View RelatedTurn 6 random numbers in 2 columns in to 1 column of these numbers from small to big.with a function so i could move it.
View 9 Replies View RelatedI created an Excel timesheet, no VBA, which 65 people are using. 64 without a problem but the 65th cannot print the sheet as others can. The last cell on the single sheet is AB61. For the 64 who can print it by selecting the fit to print on one page the percent is 71. For the one person with problems the percent goes down to 10. Even when she opens the read only version on the network it becomes 10%. I checked the magins and everything else I can think of. Short of reinstalling Excel (2000 with Windows 2000)
View 3 Replies View RelatedI have an equation below that work great except when row 2 has a duplicate value. When this happen row 5 only return the first value in row 1 and not the other duplicated values.
=INDEX($B$1:$AF$1,MATCH(SMALL($B$2:$AF$2,COLUMN(B$1)-1),$B$2:$R$2,0))
see attachment. How can I have row 5 return the following value: B5 = A, C6 = B and D5 = C.
I have a Pivot table (see picture) and in another field I want to be able to type in the Month and Year (e.g. January 2012) and return the worst performer of the 8 suppliers. I have been trying to use SMALL in conjunction with INDEX(MATCH(MATCH)) but it doesn't appear to be working.
I am sure there is a simple solution but it is evading me at the moment.
excel.jpg
This is where I am at with the formula. Maybe I can use the IF function somehow?
=INDEX('Pivot Tables'!B19:I19,MATCH(Summary!G3,'Pivot Tables'!A20:A41,0),
MATCH(SMALL('Pivot Tables'!B20:I41,A5),'Pivot Tables'!B20:I41,0))
I have the formula (found in cell "C2") on the Report sheet. I need to perform a function, but I cannot get it to work on the sheet I need to pull information from. The sheet RecapWk12 has a small section pasted (with some cells edited for obvious reasons) from the actual workbook. I can get the formula in Report cell (A10) to work on pulling information from sheet2. You can see I am getting (#REF!) in cell C2.
View 3 Replies View RelatedI am trying to get the following thing started but my loop doesn't succeed. I've got 3 columns. I need to place a formula in column C if A is empty.
Example:
A2 contains data so do nothing.
A3 is empty do: C3=B3+B4.
A4 contains data so do nothing.
A5 contains data so do nothing.
A6 contains data so do nothing.
A7 is empty do: C7=B7+B8.
this is what I've got so far:
[Code] ..........
How to get the small value excluding zeros?
small(Convention!$I$3:$I$1000,rows(Dashboard!$D$41:$D41))
When I try to record macro, the small toolbar which has two buttons "stop recording" and "relative reference" do not appear. I need to turn relative reference on.
Can you please guide me how can I make it visible to turn relative reference on?
The following code is a is designed to paste into "Y1", the value in column 5 of the active row when that cell is changed. This works perfectly if I select the value from a dropdown list and hit enter, or if I enter a value manually and hit the right or left arrow key. However if I manually change the value of the cell and then hit enter, it does not work because it looks for the value in the next row.
Does anyone have any idea what I can do to make the macro stay focused on the cell who's value changed even if I hit enter?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim AR As Long 'Active Row
AR = ActiveCell.Row
If Target.Column = 5 Then
ThisRow = Target.Row
If ActiveCell.Value > 0 Then
Selection.Copy
I have a form that has been set up at 55% for best view. The only problem I have is that all the dropdown boxes (List) that i have the text is extremly small. Is there anyway of changing the size of this text? just the size of the text in the dropdown not in the cell after the selection is made.
View 9 Replies View RelatedI'm writing an application in Excel, its a little over 5MB after I've run through UsedRegion and trimmed stuff, 94 worksheets. I'm encountering a fair number of crashes these days. My OS is fully patched as is MS Office. Running Windows server 2003 and Excel 2007.
I was wondering about the pros and cons of writing an application in one large workbook or many smaller ones. So far its been one large one but these crashes are really impeding my progress.
What's the best way to remedy these crashes or even start to diagnose?
This snippet of code works fine when I run it. When another user uses this same workbook and runs it it gives an error:
Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
This is the line highlighted by excel:
ActiveSheet.Paste
I have tried changing it to:
Sheets("Data").Paste
I wondered if there is a possibility to make this
Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
If I have a list of 6 values and a number that I want to get as near to as possible (above or below) using any, and as many as needed, of these 6 values - is there a way of calculating the nearest value? And, if there is, is there also a way of showing which values would be used?
Example:
Value 1 = 0.190
Value 2 = 0.278
Value 3 = 0.593
Value 4 = 1.079
Value 5 = 2.075
Value 6 = 2.998
Value I want to get as near to as possible = 3.644
I am currently using a sheet of paper and tallying events. By tallying, I mean placing a small vertical line on the paper, then after 4 vertical lines, I place a diagonal line across the four. At the end of the month, I add all these up. I would like to make a spreadsheet to replace the tallying, but am not sure how to do it. Is this possible? I am beginner to intermediate with Excel and use 2003, but can switch to 2007.
View 3 Replies View RelatedI am trying to create a relatively small database that is updated by users through a userform, but also has the capability to have columns (attribute categories) added or deleted without code modification.
I think my starting point, though it works for the simple case of no updates without code modification, is not good for my actual goal.
I have a data dump, on this single excel sheet their are a large number of tables, up to 100 in all, and what i need to do is extract key figures to form a summary table which i then produce stats for.
What i need is a macro or code that will automatically scan the tables, retrieve the key figures and return them in my summary table.
Attached is an example, you will see three small tables labelled Servicing query complex, servicing query simple and total.
I need to extract the blue cell, being the name of the table, the purple cell being the last figure in column c in each table (total number or processes) and the green figure being the the average turnaround. The final figure is i think the hardest to get as its the total number of figures in column c that have either a 0 or a 1 in Colum A. Note that their will be table where neither 0 or 1 will appear.
All figures are dummy figures but the table structure is correct.
