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:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
View 6 Replies
ADVERTISEMENT
Mar 26, 2009
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:
Box Quantity
- 375 pieces
Break down options
2 - 187.5 pieces
3 - 125 pieces
4 - 93.75 pieces
5 - 75 pieces
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.
View 5 Replies
View Related
Dec 30, 2006
I need a formula that would return the sum of the largest 3 numerical values out of a range of values, i.e. ignoring the lowest values in the range.
View 5 Replies
View Related
Jul 11, 2007
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.
View 2 Replies
View Related
Sep 26, 2008
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.
View 9 Replies
View Related
Jan 7, 2008
How would I find the four rows with the largest value in a defined column and then export them to a separate worksheet.
View 2 Replies
View Related
Dec 31, 2009
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.
View 2 Replies
View Related
Mar 29, 2012
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?
View 5 Replies
View Related
Dec 29, 2009
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?
View 3 Replies
View Related
Aug 27, 2008
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....
View 12 Replies
View Related
Oct 14, 2009
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.
Like this:
AAA GK 40
BBB CD 50
CCC FD 55
DDD GK 60
EEE FD 70
.....
View 9 Replies
View Related
Jul 1, 2008
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.
View 2 Replies
View Related
May 12, 2012
I have a table showing interest levels in training courses from a group of schools, eg:
English Maths Science
School1 3 4 2
School2 7 1 0
School3 3 2 5
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:
=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)
But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.
I am sure Excel has the required functionality.
View 12 Replies
View Related
May 29, 2013
Is there a way I can highlight cells that are differ from their adjacent cells by more than 10%?
Sample case:
Month 01 02 03 04 05 06 07 08 09 10 11 12
Sales 100 112 100 130 140 160 175 200 170 170 140 135
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.
View 7 Replies
View Related
May 25, 2013
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.
View 5 Replies
View Related
Oct 1, 2013
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
View 6 Replies
View Related
Dec 4, 2007
at present i have the following code that does as intended.
If number < pr1 Then
number = 0
End If
how would i alter this so that it changes the 'number' if number is between pr1 and another number pr2?
View 9 Replies
View Related
May 3, 2008
I would like to determine the values of cells in column B by the values in column A. I can do that for one cell but not for two or more.
Here is what works:
View 11 Replies
View Related
Jun 8, 2009
I have two columns in my spreadsheet:
Test1 Passed
Test1 Passed
Test1 Failed
Test2 Passed
Test2 Passed
Test2 Passed
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:
Test Status Overall
Test1 Passed Failed
Test1 Passed Failed
Test1 Failed Failed
Test2 Passed Passed
Test2 Passed Passed
Test2 Passed Passed
View 9 Replies
View Related
Apr 13, 2007
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.
View 7 Replies
View Related
Oct 19, 2007
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:
=IF(ROUND(A1/1000000,1)<>ROUND(B1/1000000,1),"CHECK","0")
View 7 Replies
View Related
Apr 25, 2008
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
View 8 Replies
View Related
Oct 12, 2012
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.
View 9 Replies
View Related
Mar 16, 2009
Looking for a "oneliner: or CSE perhaps that sums the maximum values for each subset. See example below. The answer should be "9".....
View 9 Replies
View Related
Aug 24, 2014
Data is;
83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.
So, my bullseye percentage = 43209/83300, or ~51.87%.
Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.
Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.
View 5 Replies
View Related
Oct 8, 2013
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.
View 6 Replies
View Related
Jun 24, 2014
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.
View 2 Replies
View Related
Feb 18, 2009
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?
View 4 Replies
View Related
Jan 20, 2013
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.
View 3 Replies
View Related
Jun 12, 2013
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.
datelocationtime
6/1/2013 A730
6/1/2013 A745
6/1/2013 A750
6/1/2013 B800
6/1/2013 B810
6/1/2013 C730
6/1/2013 C745
6/1/2013 C800
6/1/2013 C815
6/2/2013 A730
6/2/2013 A745
6/2/2013 A800
6/2/2013 A815
6/2/2013 B700
6/2/2013 B800
6/2/2013 B815
6/2/2013 C800
6/2/2013 C815
View 10 Replies
View Related
