Fiscal Year Stats
May 2, 2007
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?
View 9 Replies
ADVERTISEMENT
Feb 3, 2006
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.
View 12 Replies
View Related
Sep 1, 2008
writing a formula that would result in my organizations' business fiscal year.
Assuming the fiscal year is 2008, the quarters are as follows:
1st QTR = 7/2007 - 9/2007
2nd QTR = 10/2007 - 12/2007
3rd QTR = 1/2008 - 3/2008
4th QTR = 4/2008 - 6/2008
When 7/1/2007 is entered, the result should be Q1-2008
10/20/2007, Q2-2008 and so forth.
View 13 Replies
View Related
Nov 8, 2002
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.
View 5 Replies
View Related
Jan 22, 2013
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.
View 6 Replies
View Related
May 6, 2009
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.
Here is my
View 7 Replies
View Related
May 2, 2012
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
View 3 Replies
View Related
Jan 20, 2014
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.
View 1 Replies
View Related
May 13, 2014
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:
Oct-Dec = Q1
Jan-Mar = Q2
Apr-Jun = Q3
Jul-Sep = Q4
I would like for the quarter to be formatted with the year ex. FY14Q1
Here is the Code, but I have also attached an Excel file with the .bas file.
[Code] .....
Attached File : Fiscal.xlsm‎
View 3 Replies
View Related
Jan 20, 2009
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.
View 10 Replies
View Related
Jun 22, 2009
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 will attach the sheets.
View 9 Replies
View Related
Jun 6, 2009
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
View 14 Replies
View Related
Sep 17, 2013
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.
View 1 Replies
View Related
Jan 10, 2014
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
View 3 Replies
View Related
Aug 25, 2014
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.
View 2 Replies
View Related
Mar 20, 2009
i made another thread about this but this one is more detailed.
all my questions are in the attachment excel thing, on the main page.
View 9 Replies
View Related
Aug 24, 2009
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??
layout so far: (how it should be)
Stats page
racetype|selections|won|lost|placed
Sprints | 2 | 1 | 0 | 1
Non-Sprints | 3 | 1 | 2 | 0
Jumps | 1 | 0 | 1 | 0
January Page
RaceType|Result
Sprint |placed
NoneSprt |won
Jumps |lost
NoneSprt |lost
NoneSprt |lost
Sprint |won
View 11 Replies
View Related
Jan 30, 2007
I have used the "sort" function on many occasion, and I am now stumped as to why it wont work
I have a table with 9 columns: Team, Games played, Games won, Games tied, games lost, goas for, goals against, diff and points.
Each of the collums and rows have different calculators in them.
I want to sort each time the stats are modified, to show who is is first place, second etc. Thus the sort is by Points first, then Diff.
View 9 Replies
View Related
Apr 16, 2007
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.
View 2 Replies
View Related
Jan 4, 2010
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.
View 8 Replies
View Related
Oct 7, 2008
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.
View 4 Replies
View Related
Jul 24, 2013
I have data for 6 monthsone column has all the dates
1/1/2013
1/2//2013
1/5/2013
1/8/2013
1/10/2013
1/15/2013
1/21/2013
1/31/2013
I have for 6 months like these dates
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
View 9 Replies
View Related
Oct 24, 2009
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.
View 3 Replies
View Related
Nov 12, 2009
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.
View 3 Replies
View Related
Dec 15, 2008
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
View 6 Replies
View Related
Oct 8, 2008
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):
View 2 Replies
View Related
Dec 19, 2012
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)
View 4 Replies
View Related
Dec 2, 2012
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
WK 2 OCT FY2013 coverted to Oct 8 - 14
WK 3 OCT FY2013 get converted to Oct 15 -21
WK 4 OCT FY2013 to Oct 22-28
WK 5 OCT FY2013 to Oct 29- Nov4
View 4 Replies
View Related
Sep 25, 2006
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
View 3 Replies
View Related
Dec 8, 2013
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‎
View 4 Replies
View Related
