Lookup 2 Different Criteria Then Average Between A Date Range
Jan 6, 2009
For those of you that love difficult ones, here's one..
I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...
So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...
I've tried everything from sumproduct to vlookups and can't seem to get it right...
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.
I'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.
I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
I have 2 columns of data (col A is time, col B is a value), in a range A4:B1000 (can be less, but the biggest the range will get is to B1000). Example:
Im looking for a way in VBA to look for a time entered in cell C1 (example 10:38:00), find that in column A, then return the average of values in column B that are offset 271 - 300 rows. This is entered in D1. So in my example, 10:38:00 is in cell A9, return the average of B280:B310.
I have a table that looks like this (its basically a historical data of a stock exchange):
Date Index January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range. Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I want to calculate the average of a range...if it meets a certain text criteria.
For example, if the product is a "Course", then take the average of pages all those courses together.
ProductNumber of PagesExam316Course46Exam232Course32Exam245Course53Exam155Course246Exam118Course154Exam82Course434Exam80Average # of Pages for Courses = Average # of Pages for Exams =
I manage a list of web-based classes offered by my company - each class name, date, time, and attendance is recorded in the list. Periodically, we send out marketing emails for these classes to promote attendance, which is also recorded in the list.
Issue: I have a summary list of the class names (where each class is only listed once) and I'm trying to come up with a formula that will pull the class date for the last time that course was marketed.
I have attached an example of what I am trying to do - using colors instead of class names. The last date marketed for each color is already hard-keyed into the summary to show what should be there.
I have a problem getting an adequate formula for the following.
"Sheet 1" Column A.............Column B Employee Code.....Distinct Count ZZADW...............Formula Result ZZALM................Formula Result ZZALS................Formula Result ZZANG................Formula Result
"Sheet 2" Column A............Column B Employee Code.....Account Number ZZADW...............1SSS ZZADW...............1STT ZZALM................5GHL''''''''
Column B with dates and column C with values. I'd like to make another column with the cell value averages based on the date. In essence, calculating daily averages. In turn I will later be adapting it to go through the all sheets in all workbooks.
I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).
As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
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'm setting up a spreadsheet to manage recurring tasks that fall within a given date range.
I have a named variable (theDate) which pulls the list of matching tasks that fall on a single day, but some fall on multiple days, so I need to somehow extract the next valid date the task will come due.
I've been playing with formulas until my brain got scrambled. I suspect I may not have got enough sleep over Christmas and there's something staring me in the face I'll kick myself over.
Column Headings are: Description (Col B), [various notes C:E], M, T, W, T, F, S, S, (Cols F:L, marked with x when relevant), StartDate (M), EndDate (N) and my nemesis, NextDate.
That formula should check that 'theDate' falls within the range, if so, lookup whether the appropriate weekday is null or not, and if it's not, then the current date comes back. If it is, then I need it to find the next weekday (by that I don't mean M-F, I mean any of the 7) when it will fall and return that date. I do have the weekday number in F2 if needed, and I'm using a weekday return type of 2 (Mon=1).
'theDate' will usually be in the future, but not always. It needs to not fall over if it's past.
I'd like to do this via formula ideally, since the SOE I'm on keeps losing my Analysis Toolpak, but if I have to code, I have to code.
I have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
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 have a problem with writing a set of macros to automate a report.
I have been asked to automate a report that at present has the person to add data into column L which relates to the dates in Column M. the dates are in the full date and time format. eg 20/10/2008 9:07:18 AM.
For example, if the report is being run for December's Data, you would first of all need to delete any rows that contain any date after December 08, so any jobs that were raised this month, then in Column L he would add "Raised This Month" for any raised in December and "Previous Months" for any raised previous to December.
I have looked at a few diiferent ways to try this and none have even come close. It makes it harder that the month for the data will change every month.
So to sum it up I need to use the dates in Column M to affect the data in column L, also delete the row if column N is earlier than a certian date.
I am trying to get a count of cells that fall within a specific date range. In my case, I need cells from today and minus 6 days from now. e.g. last Friday until this Thursday. I do this weekly.
I can get the correct count of total entries for the period with:
I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.
So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.
In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.
I am trying to get a SUMIF formula to work where the following: - RANGE = Column N:N (These are all a list of dates) CRITERIA = Cell C20:D20 (These are the dates i want to look at) SUM_RANGE = Column P:P
Basically, I want to SUM all the numbers on Column P where the date in Column N falls between and including the dates in Cell C20 and D20
In attachement, I want to show in the YELLOW cells the SUM of Column P where the Date in Column N is between the Dates in column C and D.
I have a start date column and column for status. Status is either "in-progress" or "complete". I want to count the number of "complete" items that fall within a specific date range. I have tried countif but I can only seem to get a count of all items that fall between the date range. I do not want a pivot table for this.
I have a date range that I'm trying to get a total count on. The criteria is any date in COLUMN I that is not "approved" in COLUMN L and the date is less than the current date which I have in cell U5.
I have time-worked spreadsheet use to calculate the hours worked over a year, now I need to sum the hours worked during a given period if they happen to be on a public holiday. I have a column (say A) displaying dates from 1/1/13 till 31/12/13, another column giving the result of the worked hours (say B). I also have a defined list of public holidays which I need to compare with (named Holidays). The working year is "divided" in 13 periods of 28 days each (the last one having 29 days). I was trying to use the SUMIFS function (since i have other constrains that need to be met) e.g. =SUMIFS(B1:B28,A1:A28,Holidays)
I need to sum the 1st column from the bottom, to the same line of the max on the right column. First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments. Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either Third i tried =sum.if() but i don't have much experience with this function.
Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA instead of: =SUM.IF(I4:I25,">=2009/1/1",R4:R25)-SUM.IF(I4:I25,">=2010/1/1",R4:R25)
why can't i use? =sum.if(xx:xx,year(xx:xx)=2009,yx:yx) or =sum.if(xx:xx,year(xx:xx)="2009",yx:yx)
Attached is a really simplified version of what I'm trying to accomplish. I'll attempt to get my brain working well enough to explain it:
For each row, I have a person's name, their department, their site, and then a series of dates (representing dates of attendence). The four columns after the dates ("Counts" is the first) are me trying to keep all the right numbers in mind, and are the routes I've been trying to take on this. I have also tried to make a custom formula called ClassInRange, which isn't playing nicely.
What I eventually need to populate is the little 2x3 section at the bottom where the three sites and the two departments I'm working with (down from 5 sites and 8 departments for the sake of sanity). What I need to be able to do is populate those cells with something that will tell me how many people from which site and department have attended something within the date range.
So, for example, I need to know that the 2 guys in Wellesley attended something between January 1st and December 31st of 2007. (American date style, in case anyone's lost)
If you're able to help, please keep in mind that I'm not the end-user; I'm just the slightly crazy dude trying to create this thing and make it simple to use. Eventually, the Dept/Site box will be part of a template which is copied and pasted up to four times on a report sheet, each fed by different date-range criteria.
Also, the important information is the person's name, dept and site, which I hope gives some reason behind the organization; we want to find the person easily.
I am trying to find the median for a huge amount of data, so I have cut it down to just an example size. I have uploaded a copy of the example file.
I have three price ranges that I have to find the median for:
Under $5MM $5 MM - $25 MM $26 MM - $56 MM
I need to find the median for each price range for Signed units and Sold units. I also have to find a median within a date range (e.g. within the last six months) that includes the previous criteria.
For the first formula, I have tried using the following formulas:
The part in green will count the number of entries for the name Johnson & Freedman LLC perfectly fine. However when i add the last part in red i receive a #Value! error.
Col. W is formatted as General and has a data validation for the user only to choose Pass or Fail.
