I have a spreadsheet full to the gills of patient information. I have a separate sheet with information from the first spreadsheet like counts of a certain type of tests or diagnosis codes using a 'countif' function. This is based on all the info in that first sheet. I want to make a second sheet with the same basic setup, but only from the patients who visited during this fiscal year. Is there a code I can use to make this happen?
Let's say the date of the visit is in column A and the test type is in column N. The code I'm already using is =COUNTIF(Info!N:N,"*TestName*") How do I get this to return results only if the date of visit was within the fiscal year?
I have an issue within Excel and I really hope you can assist me in cracking it. Let me try to describe my problem;
I have a given date, e.g. January 22, 2001 With this date I need to add 4 years, so I simply did cell * 1460 (as that is 4*365), so I end up with January 21, 2005. So far so good...
Now I want to know the Fiscal Year which this month is in. Example, fiscal year 06 is from June 1, 2005 - May 31, 2006.
This means that I need some kind of calculation to see in which Fiscal Year this date is. In my above example (January 21, 2005) this is FY06, but June 1, 2006 would be FY07.
How can I do this? I experimented with using Year() and Month() and then do some logical check with IF, but I can't crack it... Who could help???
When possible the solution should not have hardcoded years, so even if I enter a date in August 2050 it should still say FY51.
When I group information in a pivot table by date, and select quarters or years, it is of course based on a calendar year. Can this be done by fiscal years?? using an addin or something, where I set the start month of the custom fiscal year.
I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.
This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place.
I am working with a fiscal year that starts in December and ends in November. I want to make a user defined function that will return the fiscal year of a date. I've created the below code, but it returns a zero.
Function FiscalYear(DateFY)
If Month(DateFY) = 12 Then Year (DateFY) + 1 Else Year (DateFY) End If End Function
I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.
As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.
So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.
I have a Fiscal Date macro that will looks at a date to give me the current fiscal month or week, but I would also like to add the functionality for it to give me the quarter.
In my business, the fiscal year starts in October. The quarters of course are as follows:
I am captain of a Dart Team in 2 different leagues. I have a excel spreadsheet that does some calculation for me (Wins, Losses, Percentage) when I manually enter the numbers. I have just created a workbook with multiple worksheets for each week of the season, including playoffs, and a sheet for overall and print. I want to be able to input the data for each week and have it auto calculate on the overall page and auto populate the print page. The overall page will have all the stats, wins singles games, wins doubles games and wins 3 man games, along with percentage in each category. I am pretty sure I can accomplish this task. The problems I am having is:
1. Adding wins and losses based on player number, I have one cell with a players number and I will put a 1 or 0 in that cell based on a win or loss. Off to the side I have the player roster with card numbers and a win column and loss column. I would like excel to see the 1 or 0 in the main column and add the appropriate to the roster column, calculating total for the night. Then adding that to the overall page.
2. The print page, I want a easy to read page that displays each players, wins and losses for each category described earlier and averages through each week displayed nicely to distribute each week. Would I have to export that page to word? I want to display wins and losses as 14-7 without it calculating it.
If someone can point me in the right direction that would be awesome. I can do simple stuff in excel, never had to do anything like this before, wanted to do it for a while and just didn't know where to start. What formulas to look up, not familiar enough with the nomenclature to look for the correct functions to accomplish this task, or if it's even possible.
I have many spreadsheets with sports stats. I pulled the information from the web. So on one page I have the stats. The next page I want to be able to type the teams name and have excel pull out all the stats I want. Makes it easy to compare teams stats that are playing each other. Is this possible I tried to use most of the lookup functions but I just cant figure this out.
I seem to find myself stuff on this project yet again, JBeaucaire was able to help me to get this far im hoping you will be able to help me with the next step of this project The doc iv attached is able to give me the differnece between low and high numbers with this example low numbers being between 1-18 and 19 – 36 high numbers and 0 being a break if u test the project u will understand im now trying to create a pattern of 3s were im abe to get stat hits on for example the patterns of 3s are the following(numbers) low,low,low-low,low,high-low,high,high-low,high,low every time I type in the numbers 3 times in a row on the 3rd one I want it to show as a hit(success) if the 3 numbers I type in are not a hit like high,high,high I want it to show me a miss if it is a pattern I want it to show a if theres a 0 it must rest like from 0miss,4hits,o breaks to 0miss,0hits,1break and so forth im also wondering if at another part of the form it can keep the total stats like iv added In the doc
Is there a way to automatically update football stats from a website. I want to update many different stats each week of player and team performances. Can I specify a website to go to update the info and prefer to do it each Tuesday. Let me know if more details are needed.
I am new to pivot tables and charts and to build.... I heard that the best is to use pivot tables but no clue how to do it I have tried but no luck....
How can I see the following information in chart?
-Non con close out time by person- so who is failing to close out on time in this case is completion date -Frequency of reports by area/ shift -Number of reports by month, split into critical/major/minor -Main reasons for reports so trend of type of record
I use a workbook that records data centered around course referrals.
The workbook also has a stats page. It records Instructor stats but needs to be in the form mostly of percentages.
In Column D within the page Stats I need the cells to show the total number of "YES" that each Instructor has achieved from their number of pupils in the Sheet Data.
Column E and F in Stats need to show the percentage of Yes and No of each Instructor from Column D in the Sheet Data.
Column G In Stats needs to show the number in total of Referrals made by Indivisual Instructors taken from Sheet Data.
Columns H,I and J in Stats need to show the percentage of referrals to OBT,Kaya and HWal made by the Individual Instructors taken from Sheet Data.
Finally in Stats, Columns K,L and M need to show the percentages for each Instructor of their Leave Reason again Taken from Sheet Data.
Usually there are several hundred entries and at the moment I do this manually with a calculator.
I having trouble trying to find the right formula to display statistics onto a 'Stats' page for my horse racing results from each month.
On the Stats page I have a summary of each month where I can view winners loosers, profit and loss, and the different types of racing (eg Flat and Jumps,Sprint, Non-Sprint).
For January I have two rows on a column for Sprints and Non-Sprints here I want to diplays the results (selections,won,lost) for each. The infomation on the January is all in date order and sprint races and non-sprints are all mingled in together. How do I display the infomation for sprints and non-sprints seperately on my stats page??
I'm struggling to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
What I would like to do is for each worksheet run "descriptive statistics" in data analysis on the data in column Q, which varies in length, and then copy the "mean, median, SD, and skewness" in the results down columns T:W to the last row based off of a count in Column Q. Finally, calculate the "zscore" in column S based off of the formula in cell S2 in the attached workbook.
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
I need to convert all these dates to week 1- week 5 week 1: 1-7, week 2: 8-14, week3: 15-21, week4: 22-28, week5: 29-31 Instead of 1/1/2013 it should show week1 instead 1/10/2013 week2 like this
Our fiscal calendar year is from Nov 1 (Q1) to Oct 31 (Q4). If I have a column of cells with each cell containing a date of the year, do you know of a way for me to be able to determine which quarter a particular date falls into? See the attached workbook for an example.
I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.
I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.
Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this?
Start End Month 09/21/08 10/18/08 Oct 10/19/08 11/22/08 Nov 11/23/08 12/27/08 Dec 12/28/08 01/24/09 Jan 01/25/09 02/21/09 Feb 02/22/09 03/28/09 Mar 03/29/09 04/25/09 Apr 04/26/09 05/23/09 May 05/24/09 06/27/09 Jun 06/28/09 07/25/09 Jul 07/26/09 08/22/09 Aug 08/23/09 09/26/09 Sep
I am attempting to check to see if the current week is the end of a period or not. If it is, I need to display some text, and if it is not, I need to display nothing.
Since I am Computer Science major, the best way I can describe this is through and if structure from a low-level programming lanuage (like C++ or Java):
I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.
I have the calendar listed on a different tab.
Fiscal Month Start Date End Date
January 1/1/2012 1/31/2012
[Code] .........
The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)
I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week
For example: WK 1 OCT FY2013 get converted to Oct 1-7
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then MsgBox ("You may not enter Data before the current Month") Else '...... Run main code here
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013 Jan 300 100 Feb 275 100 Mar 400 150 April 650 May 454 June 800 July 500 Aug 375 Sep 525 Oct. 300 Nov 410 Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
