I have problem How to find Max of a column but instead see the conttent of the previous besde column on two criteria. and some one "UMH" from another forum helped and guided me much but unfortunately still my problem remains. http://www.access-programmers.co.uk/...d.php?t=138130. I attach the sample
View 4 RepliesI have written a snooker scorebaord spreadsheet which keeps a history of highest scores and highest breaks, but am unable to find a way of showing these key values in a table.
Attached is a cut down version to demonstrate the problem. If you can provide a solution it can be either in formula or code.
i have a list of price list, and it is updated once a while, which means there is a change in version number.
The table is a direct extraction and it has to be like that...
I have tried to vlookup but only return 1 line, same as match and index... or maybe im not using the forumla correct.
Attached is the excel example hope this help. as you can see there is alot of dupe reference but different version and prices
I cannot concatenate the reference and version number as my lookup reference doesnt know what one is the latest version...
but one thing is constant, that is the version number is always needs to be the highest.
It is any code in VBA to find a Maximum value whit 3 conditions from one sheet to another ? Somethig like this:
ActiveCell.FormulaR3C7 = "=SUMPRODUCT(Max((sheet1!R2C6:R65536C6=RC[-1])*(sheet1!R2C3:R65536C3=RC[-2])*(sheet1!R2C4:R65536C4=RC[-3]))"
ActiveCell = ActiveCell.Value
I need to find a max value from a sheet if acomplish 3 conditions in another sheet.
I want to find the maximum in column C if Column A = b but I don't want the number if it equals 11 or 9. So, the answer should be 7, but I don't know how to create the correct formula..I'm sure I have to nest a couple of things into an array, but I just can't seem to get it to work.
ABC
a17
a34
b27
b711
b109
c93
c86
d78
e44
e92
e31
f27
Maximum value based on multiple variables. Basically what I have is something like this:
View 3 Replies View RelatedI am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).
Eg :
sno dept name marks r1 cs sush 55 r2 ece ram 68 r3 cs harish 77 r
So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
I have a set of data about with approx 7500 cells all contained in one column. The data has a series of peaks that happen, and I need to identify each peak value and place it in a cell. The peak does not occur at regular intervals but they are somewhat regular, as in within 130-230 data points. So, if one could find the first peak in the first 200 cells, identify it and store it somewhere, then look in the next 200, store it, etc. I don't know how to do that in excel.
View 4 Replies View RelatedWhat I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies View RelatedI have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:
INDV DATE TIME FIX ....other attributes
INDV = unique code identifying the individual
The data is sorted by INDV, then by DATE, then by TIME.
I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
My data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub
I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
see attached workbook.
I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.
1) The name is in the list
2) The code in the list is one from the codes tab.
I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status %
S1 75% and higher
S2 42.8571% -74.9999%
SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
Is VLOOKUP suitable for if one wants to lookup a value from a defined range based on two assumptions?
Enclosed is an example file.
I want to lookup the values in sheet1 in the defined range in sheet2 and write enclosure and density to sheet 1
what method to use?
nicolaiBook1.xlsx
My spreadsheet looks like this:
Column A: Individual
Column B: Not used in formula
Column C: Not used in formula
Column D: Location
Column E: Duration in Minutes
Column F: Pay (Column E)*(Column G)
Column G: Formula
(simplified version listed below)
[Code].....
To be clear of what I'm trying to achieve:
When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.
My question is:
How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?
I had tried adding these lines (with their corresponding closed-parentheses at the end)
[Code]....
But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.
I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))
but it's returning zero values regardless of the criteria being met or not.
I have a table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies View RelatedI want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
I am trying to sum numbers based on three counditions.
My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*
The yellow cell contains the formula I am using.
I currently have this equation: =AVERAGEIF($B$2:$B$900000,">="&L5,$C$2:$C$900000). Where L5 = 100. I'm interested in having the formula averageif B column value is between L5 & L6. Where L6 = 200. So I want it to find all values in B$ that are between >= 100 but < 200, and then average their C$ counterparts.
View 3 Replies View Related