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).
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
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).
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
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.
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
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).
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B Expiry Date________Redeemed by 15/08/2014 15/02/2014 15/08/2014________John 15/02/2010________Marc 15/02/2011________Bob
Here is my starting Vlookup Formula I. I need to alter it as I will state after the formula...
Code:
=Vlookup(A2,REPORT!$A$2:$D$10000,4,0)
Instead of returning the data from column D upon the first match I need the smallest available number to be returned. There will be more than one match and they will all have different numbers to return so I need the smallest return from D.
So in "REPORT" there is many duplicates in Columns A but they all have different values in Column D and I need to return the smallest number from Column of all of those duplicates in Column A.
Please see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y".
It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment.
I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use.
I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
In worksheet1 I have a set of data. Column A is a value, column B and C are dates. In worksheet2 I have dates in Row 1 going out horizontially. I would like to create a fomula that returns the value in column A in worksheet1, if the date is between column B and C. I couldn't get my array to work. it's been a couple of years since I've wrote any indepth formulas.
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number].
I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title.
Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
I have a four columns, one with created date, one with proposed date, one with a name and one with a dollar amount, I want a formula that will look at the two columns with dates and report back based on the name, the total dollars of each line where the dates are prior to today.
I have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
I want Excel to see where today's date falls and return how many work days it is from the date boundaries.For example, today is 2/8, which falls between 1/13/2014 and 2/12/2014
But I want to also grab the 2/12/2014 date and get the number of workdays from that- all in one equation.If not one equation then 2 but not separated for each border date like I have above
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ).
The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me
The problems I need solving are:
I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this?
I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:
Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.
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.
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.
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.
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
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.
I need to highlight the minimum value amount in a range. For example I need to highlight the minimum value for the values in column A, C, and E. I want to do this for lot of rows. Is it possible to use the conditional format for all the rows at once or do I have to do it one by one.
I want to find the minimum time value within a range of cells, excluding 0:00. Currently, =MIN(BL5:CP5). returns 0:00 if it exists in any of the cells.
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.
I have used max and min function to display minimum and maximum time value; however, it is not displaying the correct time value from the list. I think there is a better formula to achieve this. From the sample data displayed below, minimum time value should be 11:30PM and maximum time value should be 6:30AM
See sample data below:
4/1/2013 11:45 PM 4/1/2013 11:30 PM 4/2/2013 6:30 AM 4/2/2013 6:15 AM 4/2/2013 6:00 AM 4/2/2013 5:45 AM
I have the two buttons on userform "viewer" that try to push the listbox past it min and max range. resulting in a error. Could it be that if listbox is at min it set the button to hidden.
