Have column A which is list of names and column E which shows how much each name owes. I want to compile a list of names and amounts owed for the 15 biggest debts (ie. 15 lowest numbers as they are negative values).
View 2 RepliesI have numbers in K12 thru K23 (a few of the cells are blank)
The formula below is what I'm using to figure a golf handicap. I have 40 handicaps to figure this way.
=(((K15+K21+K19+K23+K18)/5)-37)*0.9
I manually picked out the 5 lowest numbers in column K for the formula. I think there should be a better/easier way. Can anyone help me?
I am currently trying to display a number from a column of data, where the number is the smallest, then the second smallest (third, fourth and fifth where applicable). When using =small, I am able to display the second smallest number, but when the list contains duplicates, the second smallest figure often matches the smallest. I am having the same problem with =large. I have tried to combat this by using an IF statement, but am only able to place so many arguments into the formula before excel is unable to perform the formula. This is also proving quite lengthy :o(
View 4 Replies View RelatedI am managing a group of sales reps and when they sell a product they receive commissions I am trying to develop something in excel that in cell B5 there will be a drop down that they can choose one of three products (call them A,B,C) then in C5 another drop down will be there w/ two options (new, refurb), then the result will be in D5 depending on the selections. The parameters would be:
Product A new = $10
B new = $20
C new = $30
A refurb = $5
b refurb = $15
C refurb = $25
So if the rep dropped down in cell B5 Product B then dropped down in cell C5 Refurb, the result would appear in D5 and would equal $15.
My organization whats to provide other a excell spreadsheet ( see sample attached) with pick list for certain cells. We have successfull completed adding the pick list. HOWEVER, at times we would like the end user to be able to pick multiple choices form the the pick list
In the example - in column c the user can pick from the drop down list
A
B
V
or
W
However we want the end user to be able to select any combination, single choice, multiple choice ect
In this example with the above pick list created
How can the end use pick A and W or A, V and W etc
cell a1 is blank
cell a2= "sb"
cell a3= "db"
cell a4 is blank
cell a5= "rm"
cell a6= "kw"
I want to randomly pick 1 set of initials from the 4 listed in the 6 cells and put the result in cell a7.
Is it possible on Excel to make a drop down menu which picks records out of a list?
I've a big spreadsheet. Thousands of records. About 100 responsible are listed responsible for them. I've highlighted stuff for them to fix, I'll be emailing it out to them, and they'll need to isolate the dozens of records they are each responsible for.
They are, erm, technologically challenged. I want to keep it simple, put one menu on the screen and say
'Pick your name from the list.'
George W picks himself, and then it lists his records but not anybody else's.
Can Excel do that? (Rather, probably, but where do I start?)
How can I add multiple cells (ranges) to a listbox, and when the user clicks one, it will activate that cell in excell. I thought about have the list box for the text, and a corresponding array that holds the actual range or address, but I cant get it working.
Long version:
I have a search feature that, when you type a name, it searches through a column and finds the name. Now if there are multiple matches to your string, I want it to add the full name to a listbox, and have the user pick one from the list to activate.
So basically, if I search for the name "john" but theres multiple people with that name ("john doe", "john smith", & "john mazz"), it would add the three to a list box, I would pick one from the list, and it would activate that cell.
I have everything done except that last part. I have it adding multiple matches to the list box, but I cant figure out an easy way to activate the correct cell when double clicking the item in the list. I could have it search again for the selected full name, but there is likely to be a case where one person is entered twice.
I am trying to create a quiz for myself for studying a foreign language. and I am trying to create a command button, so that when clicked, it will randomly pick from a list of names (located in another sheet) and place the name in a desired cell.
View 9 Replies View RelatedIn my attachment, I have a worksheet where I need to pick a rating from a combo box or list (Expert, Leading, Applying, Learning, Under Performing). I will be picking this rating 10 different times on this worksheet and the selections will not be the same in all cases so the definition I need won't always be the same.
I would like to return the definition (definitions are detailed on another worksheet, Data Elements) to the cell to the right.
I've tried IF statements but when I make one selection from the combo box, all the combo boxes select the same thing. I've also tried vlookup but can't figure it out and can't find an explanation in layman's terms.
I have a column of numbers and I want to copy numbers, with a constant periodicity, and paste in a new column. In the example below, I copy values from column A and past in column B, picking only every third value:
A B
1 1
2 4
3 7
4 10
5
6
7
8
9
10
I am trying to generate a list of the 5 highest and 5 lowest numbers from a list of scores that range from 1 to 10. I have found the highest and lowest absolute values (numbers over 7.5, and less than 5, out of 10), but I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers. I have used if/then formulas for the absolute highest and lowest numbers, and a rank/countif formula to rank them. I have no idea how to generate a list of the "relative strengths and weaknesses".
View 14 Replies View Relatedi 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 RelatedIn a range of 27000 records I dlike to find the lowest 5 values excluding, div0, N/A and other error things like that
View 9 Replies View RelatedI have a list of numbers in cells A1:A10, these numbers change frequently. I would like to set up a formula to calculate the 3 lowest values from those numbers and list the three lowest values in cells J1:J3, with lowest value on J1 and the highest of the 3 values in J3.
I know I can use the small/large functions for this, but im not sure how to work it out to use just the 3 lowest values.
I want to select the lowest of three values in cells I21, I22 & I23. Another condition is that the formula should not select the lowest if it happens to be a zero. If i have 0, 100 & 200 I need the formula to select the 100 value.
View 5 Replies View RelatedI'm using the code described down below to highlight 5 of the lowest values in a column. The problem; If at a certain moment the column contains less then 5 values the code errors, so I've to put in a lot of error handlers to fix the problem.
View 4 Replies View Relatedwhat I need for excel to automatically remove the highest and lowest TOTAL POINTS and create an average "Speed Rating" of the remaining 3 scores.
Is it possible to get Excel to do this?
I need a formula that will pull specified information from sheet2 (without having to sort) into sheet 1 by looking up the specified name. See attachd file.
View 3 Replies View RelatedI 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.
I have an excel with column value as below(sample)
3
4
6
7
empty cell
4
5
3
1
7
empty cell
4
5
empty cell
and more...
Actually i want it to reduce others with lowest number among them.. and after every empty cell, it should find the lowest number again and reduce it from others.. this should repeat until the column data completes. after processing it should find as below
0
1
3
4
empty cell
3
4
2
0
6
empty cell
0
1
empty cell
Any macro or excel formula for processing such a kind of excel. Empty cell works like a delimiter..
I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.
View 5 Replies View RelatedI'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
I have data in a worksheet coming from an external device that is updated via DDE. The values in the cell change every few seconds. I would like to record the highest and lowest values that these cells contain.
I want the peak values to be stored in other cells.
I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file,
View 9 Replies View RelatedI 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 RelatedTrying to arrange numerical values in order from lowest to highest in a column i have a code to do that but i need it to be able to but to a range in the column so it only sorts the values in rows 3 - 60 not all of the rows in the column
Sub Testsort()
Range("A3").CurrentRegion.Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I have imported and filtered a .csv. to specified sheet names. I have rows that have been sorted by a specific column's cell contents. i.e.
A B C C E F G H I
xxx xxxx xxx xxx xxx 1 xxxx xxx xxx
xxx xxxx xxx xxx xxx 1 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
I need to be able to select all the rows or ranges that contain a common value 1's and then loop back and select the next group 2's of rows until the row or column contains "".
I'd like to have a list printed of all the "qualifying people" found in a range, and then sort their corresponding price values from highest to lowest in Columns A and B. EXAMPLE:
RANGE: D3:D20 - Numerical
RANGE: E3:E20 - Text (names)
RANGE: F3:F20 - $$$
I'd like to search column D for any values of 2 or higher. When it finds a 2 or higher, I want it to find the corresponding name in the SAME ROW in column E, and of course the corresponding price in the SAME ROW in column F. Then I would like only those qualifying people "with value of 2 or higher" to be listed in order from highest price to lowest price in Column A, and B.
COLUMNS
D--------E-----------F---
0-----Mike Bob-----$52.65
1-----Dave Jon-----$42.50
2-----Jane Doe-----$37.65
0-----Gary Lon-----$25.50
0-----Joey Saw----$35.65
2-----Mike Jon-----$35.65
1-----Kate Low-----$38.68
2-----John Doe-----$40.00................
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
View 5 Replies View Related