I'm trying to use the Large function with multiple cells to get the highest value of the cells. Excel won't let me use the following formula.
=large({a1,f1,j1,o1},1)
Any other ways to get the largest value from multiple cells with or without an array.
I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.
if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))
Does anybody know of an easy way around this?
I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).
View 4 Replies View RelatedHow to get the LARGE function to ignore hidden cells? Is it possible?
I have a filter in a data set and am using a macro to pull in the top 5 largest values.
Is there a way to return not only one cell but two with LARGE?
I'm still working on my top 5 sellers list and trying to generate the list correctly. As of now when I have multiples of the same value I get the same name. Here's what I have now:
Name---Sales
Bob---75
Sue---68
Tim---66
Tim---66
Tim---66
What I'm trying to do is this:
Name---Sales
Bob---75
Sue---68
Tim---66
Jane---66
Dave---66
I have a group trip coming up where we took in a $1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is $15,500.00 and Triple is $14,500 per person.
There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).
Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx
Column C contains the names of real-estate brokers.
Column E contains the colors of the homes for sale.
Column Q contains the values of the homes.
I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.
I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
Excel vlookup function returns #VALUE when the lookup value exceeds 256
characters. I need a hash function to transform large strings into a value
that does not exceed the 256 character limit.
I use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:
Code:
=LARGE(C$6:C$40,1)
=LARGE(C$6:C$40,2)
ETC.
I know how to use the large function when looking for a specific criteria as the example below illustrates:
Code:
=IFERROR(LARGE(IF('2012'!$D$1:$D$7="Green",'2012'!$C$1:$C$7),1),"-")
What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:
Account Accout # Store # channel $ sales
A 1000 10001 green $100
B 1001 10011 green $230
A 1000 10002 green $120
C 1002 10021 brown $145
A 1000 10003 green $100
D 1003 10031 red $20
B 1001 10012 green $50
So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:
Account A $320
I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.
I have a column of number that I want to use the Small & Large function on, skipping duplicates.
Example:
Column A has the following numbers;
1
6
1
4
3
2
4
6
17
8
21
8
10
13
I want the Small function to identify the first lowest number (1) and then the second lowest number (2), not (1) again.
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
I read the thread Converting from Hexadecimal to Binary and what I need to do is convert the following hex number to binary; 9E6799CF. The function in that thread doesn't seem to handle that large of a hex value. Is there anyway of doing that with a HexToBin function, short of chopping it up converting the smaller numbers and recombining it in binary?
View 2 Replies View RelatedI have been facing few problems while automation my production sheet through formula.
1-- I want to sort data in the descending order through below formula.
=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))
when two candidates have the same percentage then this formula does not work.
I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.
=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))
I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
I need to add something to my macro to delete a sheet if it doesn't have more than 40 rows of data. If it has more than that, it needs to do a whole bunch of stuff to it. I've already got the whole bunch of stuff written, I'm just not sure how to do the if function. I took a stab at it, but it wants the count to be an object. I am not very good at writing code.
Code:
Dim count As Integer
Set count = "COUNT('33M & 33RUL'!A:A)"
If waffle Is "
I have 25 cells, that can either have a value of 0 or o.2 The cells have to be added up. No problem there ofcourse. But if a cell has a value of 0, -0.25 should be subtracted from the total.
I have this formula, but I'm not sure how to use it for multiple cells:
=IF(M20,0,-0.25)
If cell D3 has the text string "RQS" and cell D2 is colored green, I want to count as one. The formula needs to apply to a range of cells. The end result being I want to know how many green cells are delineated at RQS.
View 5 Replies View RelatedI want the function to return a value in A1 based on the values in B1, C1, or D1.
For instance:
If B1=1, then A1=Confirmed,
if C1=1, then A1=Ordered,
if D1=1, then A1= Confirmed.
My attempt:
=IF(B1="1","Confirmed",IF(C1="1","Ordered",IF(D1="1","Confirmed")))
I realize this is a nested IF function, and in this case does not seem entirely appropriate, evidenced by the circular reference warning.
I have a source sheet with numbers in column A, D and M. These are strictly numerical and show to be Number. I used Vlookup in another sheet to input these numbers. The column with the Vlookup is also formatted as Number. I then copied the Vlookup results and pasted as Values. Then I used Mode to find the most common number. In most cases it works, however, some cells are coming up #N/A. In the columns that were pasted as values, if I press the F2 key, then enter, the Mode formula then works.
View 3 Replies View RelatedI am currently using the function:
=IF(OR(ISBLANK(C8), ISBLANK (D8), ISBLANK (E8), ISBLANK (F8), ISBLANK (G8), ISBLANK (H8), ISBLANK (I8), ISBLANK (J8), ISBLANK (K8), ISBLANK (L8), ISBLANK (M8), ISBLANK (N8), ISBLANK (O8), ISBLANK (P8), ISBLANK (Q8), ISBLANK (R8)), "", IF(SUM(C8:R8)=0,"Yes", "No"))
and it is returning a #value error sign.
I want it to check if C8:R8 is blank, and if so, put nothing. But if not, use the formula: IF(SUM(C8:R8)=0,"Yes", "No"))
could provide me a loop function in VBA for these calcultions.
Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1
These are the calculations for E1-G1.
The process must be repeated till E50-G50
So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2
I'm currently working on a test matrix that has high level and mid level test cases. The High level results cell is tracking all mid level test cases below it. If all mid level test cases are displayed as "Pass" then the High level test cases displays "PASS". If any of the mid level test cases is not displayed as "PASS" then the High level test case result is displayed as "FAIL" However, "PASS" and "FAIL" do not accurately represent the status of the test cases in the instance of them not being filled out. Mid Level test cases start off in the "Select Result" state which basically says it that test has not been performed. I would like to have a third value displayed as "NOT COMPLETED" or "IN PROGRESS". Below is the current formula that I am using.
=IF(AND(C50="Pass",C51="Pass", C52="Pass", C53="Pass", C54="Pass", C55="Pass", C56="Pass"),"PASS","FAIL")
On thing to keep in mind that may or may not make a difference is that one of the High level test cases has 30 mid level test cases and from my understanding, IF / AND function only allows up to 30 logic inputs (not sure if that is the correct terminology or correct at all).
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results?
i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.
the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned ....
kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)
here is the formula:
{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}
"data to retrieve" is an array of one column containing the data you want to retrieve
"cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted)
"table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
I need to look at 2 different cells and have a 3rd cell provide a number based on the first two cells.
Column A: either 1 or 2 (2 results in +5)
Column B: either yes or no (yes results in a +5)
Column C: function would result in either 0, 5, or 10.