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 have letter and number combination code in two collumns and they differ for 10.000 numbers:
BAM98314 BAM88314 BAM90000 BAM80000
As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.
I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9
each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.
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 want to convert a Lexicographic Index Number, better known as Combination Sequence Number (CSN) to a combination using an EXCEL formula.C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.
I have a lookup table in cell M1:R56 which holds the correct data, that I am pretty sure about.
In cell G1 I have the Lexicographic Index Number I want converted to a combination. In cell J2 I have the value 39 (n). In cell I2 I have the value 5 (k). In cell K2 I have the formula =COMBIN($J$2,$I2).
In cell A1 (the first number in the combination) I have the formula:
=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1,$M$1:$R$56,6))
In cell B1 (the second number in the combination) I have the formula:
This works for the vast majority, but gives an ERROR when numbers 35,36,37,38,39 are in the combination, but funny enough NOT all the time.Lexicographic Index Number 575757 which is the maximum combination gives ALL ERRORS.
I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."
e.g.:
My field: 1 2 N.A 4 5 N.A 9
I want to sort the cells by descending value. But the result appears as:
My field: N.A N.A 9 5 4 2 1
But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."
EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."
I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.
Here's what I mean (though I can't figure out how to create neat columns in this post):
I'm looking for another excel game changer (for the work I do anyway).
I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?
Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.
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'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 a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).
The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.
Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.
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 using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".
So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?
Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.
The purpose of this data is for reference to question #2, so that i can utilize some percentages.
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 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 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
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as "7-1". It won't read it.
I'm wanting a function to display the largest number starting with 7,8 and 9.
I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.
I need: One displaying the largest number in the group starting with 7. One displaying the largest number in the group starting with 8. And one displaying the largest number in the group starting with 9.
Could I make use of an IF function and the MAX function?
Something like: M20: =IF("1st digit = 7", "display the largest of the list") M21: =IF("1st digit = 8", "display the largest of the list") M22: =IF("1st digit = 9", "display the largest of the list")
I've used =MAX(H5:H75), but this always displays the top "9" number.
I want something so I can show the top 8xxx number, and the top 7xxx number too.
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.