Minimum Date With Two Conditions
Nov 30, 2006
The 1st problem is a formula to retrieve the minimum date with 2 conditions, 1 of the conditions is in the same sheet the other in a different sheet. This is 1 of formats that I have so far {=Min(If(And(Assigned!C$2:C$10,000),(Assigned!D$2:D$10,000=Teams!E2)))} and is not working.
I also tried combining with sumproduct but still does not work. I have 2 sheets.On the Team sheet I have the associates names 1 time, on Assigned I have each associate names with all their sales and the date of their sales. I want to know each ones oldest date sale. I know I can sort and subtotal, but that is to much work.
The 2nd problem might be easier this is the formula =Index(A2:J32,Match(E40,A2:A32,0),1). On E40, I have the max sale, I then what to pull the date of when that occurred. I have my dates formated as date. I want an exact date of the max sale.
View 9 Replies
ADVERTISEMENT
May 9, 2008
Really struggling with this one. Can't seem to figure this out on my own. I'm trying to populate a value from a table based on two given values. The first given value will be an exact match, and the next given value sholud fall within a range. In real life terms, given the category of a security and the months to the maturity date, I'm trying to return a percentage rate to use for another calculation. The rate is given in a Table (Column I in the attached). See attached for example
View 5 Replies
View Related
Dec 30, 2011
I am trying to return the Min of a range of dates. The issue I have is a single date is repeated many times. Here is some sample data:
Sheet 1
Column A / Column B
1/11/11 / 15
1/11/11 / 20
1/11/11 / 5
1/15/11 / 30
1/15/11 / 35
Say on Sheet 2 I have a list of dates and Cell A1 has 1/11/11. In Sheet2!B1 I want a formula that uses the value in cell Sheet2!A1 and looks at the array on Sheet 1 that would return a result of 5 for the date showing (in this case 1/11/11).
View 2 Replies
View Related
Feb 2, 2010
I have a column that indicates the earliest date that a project will start. There is a Project Minimum Date, Activity Dates, Sub Activity Dates and Sub Sub Activity Dates.
When a Sub Sub Activity Date is entered, it goes up to the Sub Activity Date and so on.
I have a formula =SMALL($E6:$E224,COUNTIF($E$6:$E$224,0)+1) that will select the earliest date out of the sub sub activities and sub activities but if one of the cells is blank in the sub activity or activity, then a #NUM! error is returned.
View 9 Replies
View Related
Jan 4, 2010
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies
View Related
Apr 28, 2009
I have a spreadsheet with two columns - column A contains dates and column B has the corresponding data (always numbers). The data is not uniform (i.e there may be lots of entries in any given day and none over the next few). The data is always sorted in chronological order.
I want to set up a summary table to show the min, mean, median, max for each week. I have set up an array to calculate the mean, but I am having trouble working out a good way of gettting the others short of having a set of columns that manually define each cell...for instance =min(b3:b27) or =min(b28:b30).
View 2 Replies
View Related
Sep 30, 2009
I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.
Table is as follows
Date value(a) Value (b) ....
1/1/09 10 8
2/1/09 8 6
3/1/09 5 2
View 4 Replies
View Related
Mar 21, 2012
So I have a workbook that has a range of dates in one column and a corresponding range of times in the column next to it. On a separate sheet I want to return the lowest time&date, and then the maximum time&date. I've tried a few different things but nothing is working.I tried this:
Code:
=MIN(B:B+D:D)
This didn't work for some reason. It did return a time, but it wasn't the lowest. The maximum wasn't even close.
Previously I was just doing the min/max of the date in one cell and then the min/max of the time in a different one, but obviously that didn't work since the time wasn't going off of the maximum date, so it was just showing the lowest time period.
View 9 Replies
View Related
Dec 24, 2009
I want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
View 9 Replies
View Related
Jul 3, 2014
I'm looking for a formula that will subtract a specific # of days from a future date, and then if the answer is a weekend, go to the next business day.
For example, I want to subtract 84 days from a deadline. Let's say the deadline is in cell A4, and is 1/3/15. I want to subtract 84 days from A4. This brings me to 10/11/14 which is a Saturday. I need the date produced to be the following Monday, 10/13/14.
View 3 Replies
View Related
Jan 18, 2009
I'm using 2007 but I need this to work with Older versions so I tried to combine the condition for red
View 14 Replies
View Related
Feb 19, 2008
I want to populate two cells (the yellow ones) with the start and end date values from the row where a match is found in the 'period' column for the value in the left-hand green cell and in the 'week' column for the value in the right-hand green cell. Is anyone else on the board firing on all/most cylinders today?
View 5 Replies
View Related
May 19, 2007
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)
If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.
This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
View 9 Replies
View Related
Jan 6, 2014
I am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items
I am using Excel 2003 and my data looks like this:
Sheet-1 Sheet-2
Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days
1234 01/06/2010 - 30/06/2010 1234 -
4321 02/06/2010 - 16/06/2010 4321 -
1234 09/06/2010 - 10/06/2010
4321 21/06/2010 - 25/06/2010
1234 23/06/2010 - 25/06/2010
4321 23/06/2010 - 29/06/2010
I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.
=SUM(IF(MMULT((NETWORKDAYS(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)
*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))
*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))
View 6 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Nov 17, 2007
see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
View 2 Replies
View Related
Jun 16, 2008
i'm trying to reference a cell value in the column to the left of a "min" value:
-A-------- B
1200------20
2400------15
2300------18
900-------22
i'm using the "min" function to select min value from column B (15) but only as a means to use the A column value (2400) in another formulahelpful
View 2 Replies
View Related
Jan 13, 2014
How can I find the lowest value in a range (say B5:H20),
and then sum its column ???
i.e. The minimum value in this range is in the cell F16... So, I need to sum range F5:F20 (only the column with the minimum value)
View 7 Replies
View Related
Oct 7, 2008
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.
=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)
=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)
Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
View 2 Replies
View Related
Jan 25, 2005
I have a table populated with equations. I need to write a function to find
the smallest value in that table. However, I want to ignore the zero values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.
View 10 Replies
View Related
Jul 31, 2009
I am looking to create a formula to determine the minimum value within the top 80% of a range, but relative to certain values in other columns.
e.g.
Column A Country
Column B New Customer
Column C Revenue
I need to determine what the minimum value is for the top 80% of revenue in column C, but only including Country=UK in column A and New Customer=Yes in column B. This will tell me that the top 80% generate at least £x in revenue
View 19 Replies
View Related
Jun 22, 2006
how can i do the following equation?
40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ tO 3000$
100$ if the sum is greater than 3001$
C16 X C19 if sum is greater than 12000$
all should go in one cell
View 3 Replies
View Related
Jan 4, 2007
I am calculating several cells which in certian circumstances I want the amount to be limited to a max amount. I have a cell that will be used to indicate whether or not to limited the amount, but I am not able to come up with a viable formula. Here is my SUM Formula info:
My current calculation
=SUM(S16:S18,S21,E22:R22)
The cell that will drive the restricted amount
I7(H or S). If S is used in this cell the amount should be limited to 80. If H is used then the calculation should be exact.
View 5 Replies
View Related
Apr 24, 2007
I have a spread sheet that needs to compare numbers in a row & hightlight the lowest number in each row.
View 9 Replies
View Related
Jun 13, 2007
data in 3 colums (A2:c100)
Would like to sum the maximun values in each row together.
is Max(A2:C2) + Max(A3:C3) + Max(A4:C4) ........
I assume whatever works with max I can change to min.
View 9 Replies
View Related
Jul 10, 2007
I searched and found a couple things dealing with zeroes or other errors, but not the #N/A errors.
I have a range of numbers in A1:a100. To make this range linegraph-friendly, I have all blank or zero entries defaulted to appear as #N/A. Now the problem is that I would like to find the minimum number in the range, but the #N/A entries are messing things up.
View 3 Replies
View Related
Sep 3, 2007
has any of you happened to make a function that finds the minimum NONZERO value in a data range?
(i.e. all data are numbers, all of them are positive, but some of them are zero. The function should take the excel range as input, and return the minimum value above zero).
View 5 Replies
View Related
Jan 13, 2008
in a column let'e say A
I have the following values
Total
11
20
7
-10
30
-5
-25
16
3
21
-8
I whant to have a variable = the row number with the minimum positive value which is in this case row number 9 and the column value 3
View 9 Replies
View Related
Mar 20, 2014
If the result in cell i1 less than 1 to change it to 1
View 2 Replies
View Related
Dec 15, 2006
I want to find the minimum time value within a range of cells, excluding 0:00. Currently,
VB:
=MIN(BL5:CP5)
returns 0:00 if it exists in any of the cells.
View 3 Replies
View Related
