Determine The Largest Magnitude Of A Subset Of Four Values
Feb 16, 2010
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.
I've tried the following but got an error stating it was too long:
I'm working with box quantities and would like to get a formula that would take the box quantity and return the largest value based on some parameters. Here are my parameters:
Obviously options 2 and 4 are no good as I can only work with even break downs. This leaves me with either 3 or 4 and because I want the largest, I would choose number 3. What I need is help on a formula that will take the value of the box quantity and return the largest whole number. I think for the time being I would like to leave the break down between 2 and 5 as well.
I have a row with 30 cells and each cell has a different value. i want to count the highest 15 cells of those 30, so the sum of the total only counts the largest 15 digits.
is there a simple way to sum the ten largest (or smallest) values? I just can't think of any formula to do the job, and a filter isn't an appropriate permanent solution.
I have a column of values 2000 rows deep, and in separate column would like to determine the relative magnitude of any given value compared to the rest of the values in the column. So for example, if the highest value is 23.3, the number that would be returned is 1 (meaning this is the max value), if the lowest value is 12.4 the number that would be returend is 2000 (meaning this is the min value); if the value is the 12th highest, then the the number that would be returned would be 12 and so on.
I want to know is there any way to arrange bars in the chart with respect to their magnitude (ascending or descending) without making changes in the source data?
I have lists of numbers in a columns and would like to elliminate high outlier values in each column. Each list is different so there's no way to just set a max value and say that any value above it is an outlier. What I'd like to do is to average the top 5 or so max values for example, and then say that any vale above this average is an outlier. So a formula such as =average (max1,max2,max3,max4,max5) would work if it's possible to derive max1, max2 and so on. Or maybe there's a different way of accomplishing this?
I am looking to create a formula to add together the two highest values of a list of 10 numbers. I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent they are in random positions along a row....
I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.
I have a work document with multiple rows of information. In each row I need to locate the maximum and minimum values. (The rows aren't long - there are about 10 values in each). I then would like to turn the font of the lowest value in each row red - and the font of the highest value in each row blue. I would also like to be able to skip blank or unimportant rows.
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:
Here, In April, July and August, there is a sharp increase (>10%), while in September and November, there is a steep decline (>10%). I would like to format the sharp increases as Green, while sharp declines as red.
Can I do it using conditional formatting?
I know I can attain this by creating a separate row for Month/Month growth in above example. But this example is a simplified version of my data. Real data is a lot more complex, and I need to do the formatting without creating any additional columns/rows.
I have a large list of students (Col a,b) who do several sports (up to 10) each, which are arranged as sport name (Col c) followed by score (col d), repeated on pairs of columns for the other 9 sports.
I need a macro to find the 4 highest scoring sports for each student in turn and to highlight the score and sport cells. I have tried to find permutations using the LARGE function but I cannot see how I can do this.
I need a macro to find all of the same values in column 1 and then look to see if column 2 all of the values are Passed and if they aren't make a new column with the value that isn't passed so after the macro of formula is run you'll have:
I am trying to create a macro that determines if a range I am selecting has any non-numeric fields. If it finds say an cell beginning with a letter a message box appears letting the user know and possibly give the cell and value it found.
I have two numbers I need to compare using and IF statement. The two numbers are virtually the same, and I want the IF statement to round the numbers so any difference under 100,000 is not detected. For example:
Cell A1 = $32,194,884.45 Cell B1 = $32,194,884.37
I would like the statement to look at this number as 32.19 (divided by 1M). I am using the below equation currently, but it is not working:
Is there a function, or how would I write a vba to figure out the following.
I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36
I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
I have a sub where I pass in a 1200 row by 8 column named range. Inside the sub I want to create a single column, 1200 row range. I also pass in which column I want to use of the 8 in the big range. So I pass in a number between 1 and 8. I create a new range, but i am not sure how to reference a particular column. I am trying to avoid looping.
I have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.
I've got a table on a worksheet (daylies) with daily data, dates in A6, A7, A8, ..., various data in columns B, C, D, ... .
Now I would like to make a subset table on a separate worksheet (weeklies) with just weekly snapshots, i.e.,
snapshot from daylies off row 6 on row 2 of weeklies, snapshot from daylies off row 13 on row 3 of weeklies, snapshot from daylies off row 20 on row 4 of weeklies etc.
Is this possible or do I have to use the sledge hammer technique of copy and paste?
I'm facing a problem wherein i have two tables A& B.
Table A has two columns and 40834 records(rows).first column is userid and second column is activeyn with respective values (0 or 1) for each userid
Table B userid column is a subset of Table A and has two columns and 411 records(rows).first columns is userid and second column is activeyn with respective values (0 or 1) for each userid.
Now I want to get activeyn value from table A of all the 411 records(userid's) in Table B.
I have a list that kind of looks like this. I sorted it by date, then by location, and then by time. I need to find the earliest item in one location on a given day and display as a list.
