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
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] .......
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.
I am tying to find a way that I can get the highest value in a column of data depending on a set criteria, I have used the following =MAX(IF($C2:$C$109=$C2,IF(CJ$1=K$1,IF($D2:$D$109=$D2,K$2:K$109,0)))) and it works for the first row, I can drag it across ok, but if I drag it down it just populates the same value as the cell the formula started in even though the IF logical test has changed??
Can you please Help fix or offer alternative.
Also is it possible (say the above worked) to add to the formula so that it returns a value from another cell in the same row that it found the highest value? e.g. =MAX(IF($C2:$C$109=$C2,IF(CJ$1=K$1,IF($D2:$D$109=$D2,K$2:K$109,0)))) say this returned the value and it was found in K52 it would return me the value in B52
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
I'm trying to use countifs on a filtered table to find the sum for a column based on 2 criteria.
I've used =SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!K7,ROW(Sheet3!K7:Sheet3!K7:K20000)-ROW(Sheet3!K7),0)),--(Sheet3!K7:Sheet3!K20000="Yes"))
for a single criteria which is great, but I also need it to look at column B.
In a non-filtered table it works as:
=COUNTIFS('Sheet3 '!$K:$K,"Yes",'Sheet3 '!$B:$B,B5)
Is there any way to do this?
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
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
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 RelatedI 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.
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)
I want a cell to return the lowest value in a time series of data that comes AFTER the highest value in the range (so date specific). I have the formula for finding the highest value. The time series range changes on a rolling 1 year basis and I have attached the file. The cells highlighted in orange are the ones that need calculating.
View 2 Replies View RelatedI am trying to work out average for a particular area based on a criteria.
eg:
E F
Area time
A 01:00
B 02:15
A 01:15
C 01:16
B 01:30
C 03:15
In Cell
E100 - 103 I'm trying to work up the average of each area and using the following formula.
=AVERAGE(IF($E$4:$E$61="A",F4:F61,""))
I keep getting an the following error #value!.
i changed the average to median and still get the error.
I am conducting some analysis on alarm data of certain devices and am looking for creating a VBA algorithm that will do the following (refer to the table below for example data)
I want identify an asset in alarm (Column F) and based on its asset ID (Column C) and Fault Description (Column D) then find the row when the asset with the same asset ID and fault description returns to an 'ok' Asset State. After the row where the same asset ID and Fault description is found where the asset state returns to 'ok', I want to calculate the total alarm time based on the difference between when the asset ID and Fault description goes from 'Alarm' State to 'OK' state from DateandTime Column.
I want the result to be populated in a new worksheet that displays data with the following column headings as a result of the above calculations: Date, Asset ID, Asset Description, Fault Description, Alarm Time
I have added an additional column to show the type of output I am after.
There are up to 20,000 rows of data I want to perform the above calculation on.
A
B
C
D
[Code]....
I have a sheet with 3 columns - Line, Product and amount. I have to calculat the amount based on which line it is, and which product.
I. e I want to calculate all the amount in Line 1 except if the product is 3,23,31 (just an example - the range can be longer).
In workbook added i have shown the problem. I have tried the sumproduct formula, but cant get it right!
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I have a data set where I am trying to find the nth largest value based on one criteria? How would one use do this using the large function?
Example:
Dept. Name / Widgets
ABC / 10
ABC / 8
XYZ / 9
ABC 7
ABC / 3
XYZ / 4
If I need the third most widget production of Dept ABC, the formula return 7.
I'm trying to find a suitable formula that looks at two cells (J3 and V3) in Sheet 1 (a person's employment grade (e.g. 18) and their performance percentage e.g. 92.5%), finds those values in a table on Sheet 2 and then places the corresponding value from that table in Sheet 1 at cell Z3.
View 2 Replies View RelatedI have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
I have a workbook with a number of sheets, the first 5 are data sheets populating the other 30 (more will be added in the future).
These worksheets are renamed with a macro when the data is refreshed, allowing the sheets being used to vary week on week
The code i currently have running does a number of things, however i need it to run only for the relevant worksheets
The sheet names are either 4 digits long, or 1 digit if unused.
I need a bit of code that says run my macro for all sheets with a name of 4 characters and ignore the rest.
I have 2 columns of times (military) I need to highlite the highest time. Can this be
done with conditional formatting and how can I do that?
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.
In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?
Ok so let me set this up. I have 2 columns: .....
View 14 Replies View RelatedI have a sheet of pump test results, placed in rows, where the test rig is set at a specific speeds and flow and it records pressure. At each stage of the test a number of snapshots will be taken (Usually between 3-5 at each stage), so I have 3-5 rows of very similar data. I only need to use the best row from the selection available, so I am hoping to find a formula or code (Don't care which) that can look at the results and select the row of readings where the speed AND the flow are closest to the targetted data.
For example: In cells G6:G8 I have flow result values 0.129, 0.151, 0.156 and in H6:H8 I have speed result values 72.536, 71.82, 72.13. These are actuals. In cell N6 I have the target speed (75) and in O6 I have flow target (0.15), but these could be moved anywhere to suit really. What I was hoping to do, but am struggling big time, was to just flag up the row of readings where these two values are closest to the targets by placing a check mark in column L:L which I have formatted in monotype sorts and would like to place a "4" (Which gives me a check mark) on the 'best' row. From that I can do more with some simple IF statements.
Is this feasible, or am I trying to do something beyond Excels capability, it is certainly beyond mine at the moment. I was looking at the check mark method, but that is not essential, so if anyone has any suggestions on an alternative means of identifying the best row of data, that might make it easier to do, then I am listening.
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View Relatedautomate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
View 3 Replies View RelatedI have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.
I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.
If i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
