i am trying to work out how to use the rank formula to rank numbers in column B and keep them in unison with Column A.
So Column A has say 5 1's with column B having different scores then continuing under 1 in A is 2 and so on is there a way to continue the ranking formula without manually changing the cell ranges?
so =rank(B1,$B$1:$B$7,1) but can i do that if A =1 and then A=2 etc ?
so if A=1,rank(B1,$B$1:$B$7,1)
I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:
Screenshot 2014-03-12 21.56.18.jpg
So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.
I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER
Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x
504 that show up 3x
24 that show up 12x
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test1.Test1
End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test2.Test2
End Sub
etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub
I need some programming help to control the length of the range in the formula below:
View 4 Replies View RelatedI am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?
View 1 Replies View RelatedI have a long list (>1000) of increasing temperatures in column A. I use an array formula, {=MAX(A3:A1029-A2:A1028)} to find the largest change between two successive values. What I need to do then is obtain the two values that give rise to that difference (for instance, they might be in A678 and A679). I can't figure out a formula for this.
I can manage it if I insert a "difference" column in B, and use:
=INDEX($A$2:$B$1029,MATCH(MAX($B$2:$B$1029),$B$2:$B$1029,0)-1,1)
=INDEX($A$2:$B$1029,MATCH(MAX($B$2:$B$1029),$B$2:$B$1029,0),1)
but I'd rather keep the number of columns to a minimum.
I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell. For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50)
So far, so good.
However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).
I've tried an if statement with AND function with no luck either.
my spreadsheet looks like this:
A1 = "One", B1 = 17/08/2013, C1 = 1000.00
A2 = "Two", B2 = 16/08/2013, C2 = 500.00
A3 = "One", B3 = 17/07/2013, C3 = 1500.00
A4 = "Two", B4 = 13/07/2013, C4 = 400.00
A5 = "Two", B5 = 14/07/2013, C5 = 300.00
"One" represents the total that needs to be paid and "Two" represents the payments.
What I want to do is recognise when "One" is present and deduct "two" from that amount, but the whole spreadsheet is never the same (the data is being pasted from somewhere else) so it's really, really difficult to do that.
So I want to do =1000-500 (for that period, so in D2 it should say 500) and then =1500-400-300 (for the other period so in D5 it should say 800).
NB: Any payment ("two") that comes in after the 17th of the month will be carried forward to the next months "one".
I have a database of approximately 250000 email addresses and I would like to see if there is some way to use Excel to analyse the strings in order to identify potential 'non-unique' individuals.
For example, if I have the following email addresses:
12345@example.com
12346@example.com
12347@example.com
or
example1@example.com
example2@example.com
etc
Is there some way of extracting common text strings, lets say where there is a difference of 1 character / digit between strings (rather than alphabetising and searching manually)?
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
View 9 Replies View RelatedI have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1ContainerID26 11135 8 11245 7 11355 7565 6 9 11675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
I have a data which contains:
A B
1 Name: A
2 Address: NY
3 Gender:Male
4 Cell Numbers: 123
[Code].....
I know that this has been asked a million times, but I need the new table in a new sheet (which is sheet 2 and also I skip the Cell Phone numbers data).
I am looking for the easiest way to find duplicate Work Order numbers that exist in 2 separate Workbooks. EX. Workbook 1 Sheet one contains the numbers 1-100 in A1:A100
Workbook 2 Sheet one contains X amount of the numbers between 1-100 located.
somewhere in A:A. For arguments sake let's assume those numbers are 3,6,33,87,99.
What would the formula be to return the values that are in both of the workbooks?
I have small table i would like to create. Now, it can be done manually ( but its be very very time consuming) but im sure of a way using IFs and VLOOKUPs so that the data selection can be done automatically...
so in column 1 i have various valuations from 0 to anything 50mil plus that i need to then separate into 4 different columns based on their size. so column A would have 0 - 250k, column B 251k to 500k, column C 501k to 1million and etc etc...
vals 0-250 251-500 501-1mill
555,000 300,000
150,00 75,000
Please see attached ... testing values 1.xlsx
I have attached a sample sheet which deals with property sales data, in reference to a two-part question.
1. If the row has two, or more, rows share the same value in column 'E', it needs to be identified with a 'Y' in Column G. In looking through old threads, this seems possible, though I could not find and answer I could 'bend' to work. If this is possible, can the following be included?
2. If two or more rows share the same value in column 'E', list the identifiers (value in Column A) for the others in Column H, separated by commas "," or slashes (preferred) "/".
ie.
"00370600000700 'NICHOLLS JOHN W & CARLA R 11/27/2000 85000 '260647 W Y '00370600000800" and
"00370600000800 'NICHOLLS JOHN W & CARLA R 11/27/2000 85000 '260647 W Y '00370600000700" or
"'00370500000801 'FRAHM FREDERICK/ERIK/KRYSTYNA 06/17/2004 110000 '288904W Y '00370500000802/'00370500000803"
The sample sheet attached includes 26 rows of data with several 'doubles' and one 'triple' 'duplicates'.
Please note that in the 'real' file, it has slightly less than 200,000 rows and I have seen 'dupicates' up to 40 with the same value in Column 'E'
I am trying to slim down my database results in Excel via MS Query by searching for Part ID's that are numeric (we have parts that also contain letters....I want to weed those out).
In all my searching on the web, I thought the ISNUMERIC() function should be the function for this, but I keep getting an ORA-00904::"ISNUMERIC":invalid identifier....
Is this function supposed to work or is there another function that will do this
This is my SQL statement so far, which works to get parts that are 6 characters long only:
SELECT PART.ID
FROM SYSADM.PART PART
WHERE (LENGTH(PART.ID)=6)
When I change it to this to get parts that are numeric, it gives the error above:
SELECT PART.ID
FROM SYSADM.PART PART
WHERE (LENGTH(PART.ID)=6) AND (ISNUMERIC(PART.ID)=1)
I currently have a database that is similar to the example I've pasted below:
TypeDateTimeA10/01/200910:00B10/01/200910:00C10/01/200910:00D10/01/200912:00E10/01/200912:00
As you can see the Date and Time are repeated for several rows. This is how the data I receive comes through as A,B and C refer to a single transaction and D & E refer to another transaction.
Ideally what I would like to acheive is:
TypeDateTimeTransactionA10/01/200910:001B10/01/200910:00C10/01/200910:00D10/01/200912:002E10/01/200912:00
Where Excel can compare the date and times of each row and look for matching rows above and below it and then fill in a column next to it indicating that x number of rows are linked to a single transaction - preferably labelling them in some order to I can tell how many transactions there are.
I've created two large historical tables on separate sheets. The master sheet (Bins) contains the following columns:
Year - Side - Commodity - Variety - Block and then each consecutive column is a day of the year, starting at 4/26 and going through 10/31 (F - GL). Column GM is a sum of all days in each row.
For example, GM15 totals F15:GL15. The second sheet (Percentages) is identical to the first in size and format, however, it calculates daily percentages of totals from the first sheet. For example, cell BA15 contains the formula
I have been tasked with converting our old inventory system into a new one. Here is an example of the conversions I need to do,
I need to convert this:
A1 A2
1.50 OD x .035 W 304, WD ASTM A-269
1.50"OD X .065 W 304,SMLS ASTM A-269
1.50"OD SCH 40 304, SMLS A-312
INTO
A1 A2
304-WD-ASTM A269-1.50X.035 304 WELDED ASTM A269 1.50X.035
304-SMLS-ASTM A269-1.50X.065 304 SEAMLESS ASTM A269 1.50X.065
304-SMLS-ASTM A312-1.50 SCH 40 304 SEAMLESS ASTM A312 1.50 SCH 40
The current plan is for me to do this manually over thousands of entries over the next few months, but if I can do it in a quick fashion that would be way better.
here is some code that I have,
Dim zima As Range
Dim Col As Long
On Error Resume Next
Set zima = Application.InputBox("Select column to paste to", Type:=8)
On Error GoTo 0
If zima Is Nothing Then Exit Sub
[code]....
Where in the formula C[-7], I need this to line up with the column that I selected via the InputBox. If I select column L then the -7 needs to change to +2. OR would there be a better way to write the formula in VBA?
I recently made a formula of net sales contribution (sales per product category/total sales). For weeks 1.2008 to 30.2008. But I have several product categories. I would like near the value of the formula (not in another cell!) to insert a rank of these contributions. I d love to have the rank inside a parenthesis() near the percentage.
Here is the format
NET SALES CONTRIBUTION PER PRODUCT HIERARCHY 239.200740.200741.2007
COM RETAIL MEDIA0,03%0,73%0,22%
DTM STORAGE MEDIA0,00%0,06%0,00%
Of course I dont know how to do it.
i rank some thing
stock 25
A B c D
rank style qty
1 st1 10 for d i need go to rank 1 first then qty 10
2 st2 5 then 2 then 3 till 25 (stock ) after it comes 0
5 st3 4
4 st4 3
3 st5 6
it can posible with rank and min formula
I want to find the rank of a year that falls between the start date and end date. For e.g.
Start date : 1/1/2005
End date : 10/31/2014
Target Date: 12/20/2009
Using year(start date)-year(end date), I get the number of years but how to evaluate the answer in a cell to get the result = 5 (my desired answer as 2005 through 2009 = 5 years).
So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?
So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.
I am involved in a software conversion that is taking 4 full time folks over 5 weeks to clean up and assign an alpha-numeric sequential number to each vendor, client. Each scrubber is reviewing an excel spreadsheet containing the names, addresses, FID, telephone, etc. of our vendors and customers. This information is being pulled from 2 separate sources. We are assigning a BP # to the main office location and not retiring that one. then we go on to identifying the dups. All dups get a Y to be retired, but if they have a different address then the main one, we place a Y to bring that address over under that BP#.
Ultimately, we end up with 2 systems combined into one dumping all old numbers assigned and giving each vendors, customers, etc. a new BP# that may have muliptple addresses.
How can we assign a alpha numeric number without going through each individual line...over 900,000 of them to do. Key is to identify duplicate addresses and duplicate names. Some names might be RK Electrical or Robert King Electrical but the address will be duplicated usually.
I am using the following formula to rank a list of data:
=SUMPRODUCT(--($A$2:$A$140=A2),--($B$2:$B$140=B2),--(M2