Obtaining Largest Number In A Range But Then Pulling Back Name Of Team
Oct 16, 2013
I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...
I'm looking for a formula to enter within a particular cell which will return the largest number that appears within a range of cells, for example the range H:133 through L:136. If it's any easier, only one number will appear within the range, though it could appear in any cell within that range.
I have four Columns A, B, C, D. I need to pull back the value in B when the following is met: A is the same value Closest value in column C to Column D.
I need to create a macro that will examine a range of cells, for example D12:T12, find the cell with the largest number value in that range then set the font for that cell to bold (or red, or change the cell background color).
I am a new to VBA. I want to add a range of numbers to another range of numbers. However second range should always add to itself with first corresponding array values. For example my firsr array is A1:F10 and second array is A15:F25. Now i want A15 = A15+A1, A16= A16+A2, A17= A17+A3 and so on. Every time second array should retain itself value and get added with first corresponding array values.
I am going to create a large drop down list with data validation the list is to do with fitness the list will containt a sport with a numeral after it for example : GOLF 4.5 then under that Basketball 12
what i am wanting to do is make a calculation based on what sport is selected in that cell for instance GOLF 4.5 is selected in the cell so i need to say valueinA43*D43/B9 can this be done?? Or if not. can i extract the value from the list to another cell without the text and then perform the calculation.........
I've got a problem where I need to assign values to ranges between 0 and 3.
Eg.
RANGE VALUE 2-3 2.2 1-3 1.6 0-3 0.8 0-2 0.5 0-2 0.5 0-1 0.1 0-1 0.1 0-1 0.1 0-1 0.1
The values above are simply approximates done manually but I need it done automatically using a formula. The resulting values should be proportioned similarly to the ones above but they need to all sum up to 6 and be no more than 3 each.
I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.
In column A there could be multiple entries of the same ID number.
In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.
I need to export (to another sheet) any entries with the following conditions:
A = ID number with any instance in B showing both a 243 and 400 condition.
E.G
A B C D E AA565677878D - Pcid243B - C - D - E AA837632823G - Pcid243B - C - D - E AA837632823G - Pcid243A - C - D - E YA74846CC - Ddar400A - C - D - E WW768765364F - Ddar400A - C - D - E WW768765364F - Ddar400B - C - D - E WW768765364F - Pcid243B - C - D - E AA73262362G - Ddar400B - C - D - E
In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:
A B C D E AA565677878D - Pcid243B - C - D - E AA837632823G - Pcid243B - C - D - E AA837632823G - Pcid243A - C - D - E YA74846CC - Ddar400A - C - D - E WW768765364F - Ddar400A - C - D - E WW768765364F - Ddar400B - C - D - E WW768765364F - Pcid243B - C - D - E AA73262362G - Ddar400B - C - D - E
Now I've spent ages trying different things and can't get anything to work...
I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.
I would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes hundreds of thousands of rows so I was wondering if there is a faster, more efficient way to do this.
write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.
[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]
I've a column that contains numerical values. In my vba code, I have to select a block of cells at a time and get the row which has the maximum value. One lame approach I am doing is wasting another column that extracts the max from the block of cells using Excel's Max function, and then doing a iterative search in the numerical column to get the cell tht contains the max value, and from it get the row number
Is there a better way such tht i can avoid the looping?
so in a nutshell, i want to get the row number from a range of cells that contains the maximum value
I have huge sorting job in Excel.I have a excel file from each year.And I put these together to a huge excel file. And I need to track down the last activity if the customer is listed multiple times.I could just go through and delete the rows, but the last row.But, that takes a lot of time.
Format: Last activity (year only) - Customer number - Customer name
2010 - 1001 - Company A 2011 - 1001 - Company A 2012 - 1001 - Company A
I am trying to subtract the highest number from a row of numbers then find the average for the remaining numbers in Excel 2003, as: =SUM(B2:E2)-MAX(B2:E2)AVERAGE(B2:E2).
I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:
12
123
234
12346
2345
[code].....
So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.
I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.
I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:
I am trying to get the addresses of different cells containing numbers. This addresses should be ranked by the size of the value from largest to smallest. Currently I am using this formula
and just increasing the variable of the LARGE function for every rank. This works fine up to the point some numbers are the same. If I have for example two cells containing a 80 and 80 is the largest value, rank 1 and 2 result in the same address (of the first cell containing the 80). But I want the formula to result in specific addresses for every number. I thought about using MAX instead of LARGE, but I don't get a working solution for the second (third, fourth, ...) largest value.
I tried a few searches for what I need but I think my wording might be off or there isn't anything on it yet. So the formula I'm looking for is: I have a single colum of say 700 rows with random values in each. I would like to average rows 1 to 15 then 2 to 16 then 3 to 17.... all the way through to 686 to 700. Now out of all that I need only the highest 15 period average value returned. Seemed simple until I tried to do it. Whats that saying, the idea is 1% of the invention..
I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below
Red Green Blue Orange Yellow Result
2 4 3 6 1
[code]...
I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers