Find Highest No. In A Column But Inclusion Is Based On Data In Other Cells
Oct 26, 2009
I’m keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes it’s only 50. I’ve used MAX to find the highest score in Column D of a spreadsheet and it did what I required. However I now wish to find the highest number in Column D - but only include rows if Column C = P and column E =100.
Col B Col C Col D Col E Col F
date Location score out of % hit
25/10/2008A6210062
08/11/2008P3510035
22/11/2008A4210042
06/12/2008P6610066
20/12/2008A6010060
03/01/2009P295058
17/01/2009A5110051
31/01/2009P4010040
In other words I want to find the highest score for location P but only if that week it was out of 100 shots. It would return 66. Then I can do the same formula for location A and it would return 62. I can’t see how to do this and have searched the forum to no avail. It doesn’t look like I can just use the MAX anymore and I’ve tried incorporating that into a (nested) IF but unsuccessfully.
View 2 Replies
ADVERTISEMENT
Mar 20, 2014
Im looking to find the max or highest time in column G based on the criteria (date selected and name) So if the date is the 10/02/2014 in G10 it would look up that date and the name and bring back the time in column H. I have put an example answer in H12.
View 9 Replies
View Related
Jun 19, 2014
I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.
In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))
I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E
However, I also want to identify the subject in which they scored their highest grade in column F
I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student
2. I don't know how to write a formula in case there is a tie (see DAVID L)
View 11 Replies
View Related
Oct 5, 2013
I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.
1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00
View 3 Replies
View Related
Dec 5, 2008
In sheet 1:
I have a list of customers and their current credit limit
In sheet 2:
i have a list of all payments received in the last 12 months.
my customers credit limits are set by multiplying their highest payment in the last 12 months x 1.25
(e.g. payment of $1000 x 1.25= limit of $1250)
what i want to do is:
1) look at the customers in sheet 1
2) check sheet 2 to see if they have made a payment
3) return their highest payment (if any) in sheet 1 Col. C
4) i can work out the rest
View 9 Replies
View Related
Mar 8, 2012
I have two columns each having 8500 rows.
A= Zip code
B= Sales
Duplication of zips appear in column A - and I only need to return the highest sales in B. So based on the example below..I only need to return $500 in column C.
A
A2=50201
A3=50201
B
b2=$345
b3 $500
View 4 Replies
View Related
Sep 12, 2012
make and loop that loops true all values in column A and B. For example, if the cell A3 is higher the cell B3 then I want it to write the higher value in new sheet.
View 2 Replies
View Related
Oct 17, 2009
I have a cricket excel sheet that contains batsmans scores over a season. These scores are kept in non-contiguous cells for each game (ie. D5, J5, P5, V5 etc..) In the cell next to the score is an option for the user to enter an '*' to denote a not out score (these are in E5, K5, Q5, W5 etc.)
I can sort out a formula to find the highest score (from D5, J5, P5, V5) and place this high score in a cell elsewhere - but what I really need to do is to check if the high score is not out by looking to see if there is an asterisk in the adjacent cell, and then place the score AND asterisk in another cell.
View 3 Replies
View Related
Aug 18, 2014
I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.
What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.
For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.
So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.
View 7 Replies
View Related
Feb 4, 2010
In the attched sheet I have a list of employee ID's in column A and the Employee's Line manager ID in Column B. In Column C I need the Line Manager at the top of the pile so to speak. These line managers are listed in column J (J2:J6)
At the moment I have been writing formulas accross 11 columns (there are 11 possible levels) to check the line managers ID in Column A and see if their line manager is in the top manager list, I do this formula for all 11 columns until the line manager in the list is found. If the Line Manager is in the list I simply repeat it for the next columns. The end result is that in the 11th column all employees will have one of these Line Managers from the Top List in their row.
Is this possible to do through VBA? I have thought about how I could do this through VBA but I have just hit a brick wall.
I'm not asking for someone to do all of this for me but if someone could give me a couple of hints around how to look up a value in a list through VBA and if it is even possible to repeat that process per line until the match is found, that would be great.
View 7 Replies
View Related
Jun 14, 2013
I am trying to determine for each account number in column A, the most popular method for accessing our services based on visits in column B. And then having those numbers summarized as a total for each method.
See below for an example. As you can see Mobile was the most popular for 2 clients Java was for 1 and Desktop was not for anyone. Not all accounts will use all methods.
Account
Visits
Method
1
10
Mobile
[Code] .......
View 5 Replies
View Related
Mar 12, 2014
I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:
Screenshot 2014-03-12 21.56.18.jpg
So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.
View 3 Replies
View Related
Feb 17, 2014
I can't find a way to sort my data. It looks similar to below. And I need to sort them based on the numbers from lowest to highest.
Tom Simmon (23.2)
Jason Paige (19.1)
Jeff Johns (14.1)
View 8 Replies
View Related
Jul 28, 2014
need to understand the easiest way of highlighting the highest and lowest number in a range of cells several times in the same column.
As I said the cell range will be in the same column. So highlight the highest number green and lowest number red in cells a3 to a9 and then repeat the same process again in cells a11 to a17 and so on.the cell references are an example.
So I have several separate sets of numbers in the same column.
View 3 Replies
View Related
Sep 24, 2006
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
View 4 Replies
View Related
Feb 27, 2012
I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.
A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX
I had thought an IF formula would be the way to go.
View 6 Replies
View Related
Dec 26, 2013
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click()
Dim my_name As String
Dim r As Variant
Dim l As Long
sFind = DockDoorCal.Value
If Trim(sFind) = "" Then Exit Sub
[Code]....
View 4 Replies
View Related
Apr 1, 2009
I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.
Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.
I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.
View 9 Replies
View Related
Aug 13, 2014
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
View 2 Replies
View Related
Jan 29, 2014
Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?
What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.
Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul
[code]......
So for example:
row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July
row3: Feb is latest imput and "next" also 2. Result should be Apr
row8:May is latest input, "next" is 6. Res should be Nov.
Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.
View 9 Replies
View Related
Feb 4, 2014
I have a sheet which I need to arrange and it looks like -
Column A
Column B
Column C
Column D
[Code]...
the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"
Output should be -
Column A
Column B
Column C
[Code]....
so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.
View 9 Replies
View Related
Dec 24, 2007
I have a massive amount of data made up of values taken every five minutes over several months. I want to find the highest value in a day for every day. I'm wondering if the best method is to define each day as a dynamic range and then use the max value command to get the highest value?
View 6 Replies
View Related
Mar 31, 2009
I have a large spreadsheet of data that contains 3 columns: Columns are Name, Revision, # of items. I want to find the # of items for the highest revision for each name. List looks like this:
Smith, 1, 30
Smith, 2, 36
Smith, 3, 18
Johnson, 1, 125
Johnson, 2, 130
Lopez, 1, 8
Lopez, 2, 12
Lopez, 3, 15
etc…
I'm only interested in the data for the latest Revision. There's over 500 names. A lookup or pivot could be possible?
View 3 Replies
View Related
Nov 8, 2009
using vlookup I am trying o find the highest value>>>>
e.g
Column A Column B
111036 01/05/09
111036 08/08/09
111036 09/10/09 <<<<
Is vlookup the correct way to go,,, if not could someone point me in the correct direction
View 4 Replies
View Related
Nov 20, 2008
I have a spreadsheet with a data sheet and a second sheet. On the second sheet I want to look up the value from A2 and find the highest date associated with this value in the data sheet.
This will be used to look at a number of projects that have multiple dates, and I want the latest date i.e. the furthest into the future. To add a spanner to the works, some dates are recorded as N/A so I obviously want to ignore these. I have attached an example workbook if anyone has a couple of minutes to take a look.
View 3 Replies
View Related
Jun 18, 2009
The following calc works perfectly to find the latest entry in a column of meter readings in one worksheet and return the value to a master worksheet.
View 4 Replies
View Related
Apr 21, 2007
is there a way to write a macro that will do a find and replace and increase the find value by 8 and increase the replace value by 2 each time?
View 2 Replies
View Related
Mar 8, 2014
I have the following 2 formulas that determine the top 2 scores and return the respective headers, but they aren't perfect.
=INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))
The above returns the max, however doesn't take into account scores that are a tie or 0 values
=INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))
The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).
How can I fix both of these equations and also determine the 3rd highest score?
View 5 Replies
View Related
Mar 10, 2009
s/s is 325501 rows deep. Column C contains names. Column J contains scores. I need column N to give me the highest score a name has previously achieved. (please see small attachment for illustration). If i can get a formula then I can fill this down.
View 4 Replies
View Related
May 12, 2009
I have 2 columns, A (has the names of employees) and B (has the month in which the employee has a project scheduled). What I’m looking to do is find the latest month an employee has a project scheduled. Note: Employees can have a multiple number of projects so they may be listed multiple times with corresponding months.
I have each employee listed in column F and am looking to find their latest project month in column G. (I have the number that the formula should return in column H).
View 3 Replies
View Related
