I have a SSheet that determines when it is time to change air conditioning filters. It's based on duration of time and usage. Every filter has a different periodic cycle.
I want to incorporate a formula that will shorten the duration during certain seasons of the year. (shorter duration when AC is running more, such as hot summer, longer in Spring and Fall) .
I am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.
Ive attached a worksheet to explain it better.
I should probably mention, I want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.
Attached File : Test.xlsx‎
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I have a table of times which shows how long it took someone to process a form. If this value is longer than 10 minutes the cell next to the duration time contains the value 'Break Over Ten'. This is only true when the time is outside of 12:00 to 14:00. If the break is within this time period it shows 'Lunch'.
Is it possible to sum all of the time values that are over flagged as 'Break Over Ten'? This is how it looks
Event Date Start Time End Time Duration Breaks Duration Of Break
19/12/201308:43 08:43 00:00:35 00:00
19/12/201308:43 08:44 00:01:03 00:01
19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13
19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13
I was thinking of using SUMIF but can't work out how to total the values in the duration of break column when the cell to the left reads break over ten. Is it possible to do this?
I have an issue on getting the month, day and year if the dates is Australian time.
example :
01/04/2014 i need to have month on column D which is April and Day is 1 and year is 2014 and whenever i reformat that dates into Text(a1,"mm/dd/yyyy") it is always looks the same. and if i change it directly to format into "mmm" in column D i have January not april.
Exported data in PDF and converted to Excel worksheet and some of the data contains a space in the year.
Example: 2/6/2 014 2:49 PM.
I used the =INT(A1) to strip the time off. Worked wonderfully. However when I need to sort by ascending/descending date , it does not put in order due to this extra space. Is there a formula out there that I can use to delete the space? All of the data doesnt appear this way. I am sure it is due to the conversion.
Basically, each date, in the format mmm yy, has a cost, two cells right of the date. I want to create year sub totals at the bottom of the cost column by looking up the year element of the date....so in my own wierd logic, I want to ask, if in the range of cells C5 to C22, contains the year 10, add the cost in column E to the total cell (E27).....this is probably laughably simple to you guys, but when you dont know the correct term for the action, it is a swine to find.
View 5 Replies View RelatedI am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
I have 1 cell where i am entering month like 2 (for FEB) and in other cell i am putting value 2012 (year)
I have 1 more cell where i have to manually add 1st date of the month like: 01-02-2012
Can I merge first two values and let excel automatically return 01-02-2012 in my desired cell.
Also if i need to convert 02 to FEB or 03 to MAR then what is the formula for this.
I am using a formula for weeknumbers and formatting cells based on their values but have just realised it will not work at year end.
The formula in D2 is as follows:
=IF($C2<$O2,"green","blank")
C2 is the weeknumber of an incident based on the actual date in B2 (e.g. 01/06/2014) and uses the formula: =WEEKNUM(B2). O2 is the weeknumber of the current day and uses the formula: =WEEKNUM(NOW()).
So cell D2 states "green" if the incident happened over a week ago, and states "blank" if the incident happened in the current week.
If I used this at year end when the incident weeknumber may be 52 and the current weeknumber may be 1 - the formula would not work as it would state "blank" rather than "green", even though the incident happened over a week ago.
How can I get the year encorporated within the weeknumber?
As in the attachment example, I use a database (sheet2, where data start from row 3, and headers are in rows 1,2). I've named it as "Data" with a dynamic range name (formula: FFSET(Sheet2!$A$3;0;0;COUNTA(Sheet2!$A:$A)-2;COUNTA(Sheet2!$2:$2)). When I enter a month and a year, in sheet1, I want to get for that year's month:
1) The working days
2) The sums of the fields Totals and each of the other (AA, BB, CC etc.).
Please refer to attached worksheet.
I have a value that has a whole number and a decimal. From this number I need to figure the Year and month. The worksheet has constant values from which this can be determined.
I'm trying to find a formula that will give me a count of all programs by month that have an evaluation result. So, in column A1:A24 I have a list of programs with. In cell A25, I have the total number of programs. In column B1:B24, only 7 of those programs have evaluation results, some of the rows are blank.
I know that I can get a sum of the programs by month, but how do I only get a count of those with an evaluation result? What formula can be used to get a year to date (a running count), not a count based on 12 months? Meaning that we would need a year to date for each month, so currently for October to June (based on fiscal). If we based it on year to date, then I think we would have to divide be 12 and that would make the results inaccurate.
I have a sheet with the following values:
280315 085B 10:24 2R
The '085' is the 85th day of the year. I would like a formula that reads that part of the data, and return the month that day is in, in the "jan", "feb", "mar", etc. format.
How can I lookup the value depend on Which year in the period?. I have prepared clean data attached. I use date calculation to find how many days in the period and then divide by 365 day to count the year. But it doesn't work.
View 5 Replies View RelatedI would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like:
TermInterest rateMaturityBalance
24 Months7.12%11 November 200740,000.00
12 Months7.74%13 November 200750,000.00
24 Months7.05%10 January 200853,889.12
12 Months7.85%11 January 2008120,000.00
12 Months8.02%22 March 200817,000.00
36 Months6.68%30 June 200832,000.00
I'd like to have something like:
Nov 07 90,000.00
Dec 07 0.00
Jan 08 173889.12
and so on...
Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right
I have a workbook with two spreadsheets in it. On the second sheet there is a large table, which column headings are months and years (e.g. Jun-07 Jul-07, Aug-07, Sep-07....). I was trying to write an excel vba code that would search the first row (column headings) to find the current month and year, and copy the corresponding column along with two previous columns (months) to the first sheet. I would like to have a code that will be able to do this in Jan-08, Feb-08, or Jan-09 as well.
View 2 Replies View RelatedI want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.
There is of course several ways to do this, but is there a simple and easy formula one can use.
I want to do is copy all rows from the worksheet DATA based on column G (Date) and copy it to a new sheet based on the date (all 2003 on the 2003 sheet and all 2004 on 2004 sheet.....).
I have already created the new sheets including headers minus data. I would like to have all the data moved except the last to Columns AW & AX. I have two hidden sheets in this workbook. Would it be possible to have it auto-populate future entries from the "DATA" worksheet to autofill onto the new sheets?
Just noticed that I titled the Thread with Move but what I am asking for is copy. I cannot change the Thread title.
I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.
My finacial year starts on the 1st of October of each year and ends 30th of September each year.
My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.
I need to calculate the week numbers of a date based on the above two criteria.
I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!
so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E?
I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)
formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.
Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...
is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?
The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.
I have 5 years of data in 2 columns:
Col A. Col B.
8/2/2004 Value 1
to
7/31/2009 Value n
I have a table set up as follows
1 2 3 4 5 6 7 8 9 10 11 12
2004
2005
2006
2007
2008
2009
I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...
I tried the following to no avail... I'm just getting a zero value:
=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))
My spreadsheet uses alternating columns for actual and budget inputs
I would like to preload the budget for the whole year
But the ytd formula should only show results to match the input of actuals
In otherwords, If I input actuals for period 1, the YTD formulas for actuals and budget should return YTD period 1 only and ignore the remainder of the budget inputs
After period 2 actual inputs, the formulas shlould update to reflect period 2 YTD and so on and so forth
How can I accomplich this?
Sample of layout attached
Totals at Cols AK-AM
I have the following variables in these columns
Column 1: Ship (1064, 1065, 1066 as the field contents)
Column 12: Date (21-Feb-08 as format)
Column 13: Weld Length (1000 as format)
Column 15: Defect Length (1000 as format)
What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned.
There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc
Refer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?
Attached image: Capture.JPG‎
I have a worksheet called "zz_Exchange" that contains the exchange rate for my company for each month of the financial year. I am using this info in a summary page that uses the data for multiple calculations in the workbook.As we pass each month I would like to move the focus of a cell in the summary sheet to the relevant month in "zz_Exchange"
The column names are "currency_code" (USA Euro etc),
"currency_year" (09, 10)
"period" (1-12)
"exchange_rate" (this is where the data is held)
I have a column with dates of July 1 of every year. How can I use conditional formatting to shade a cell a certain color based on July 1 of every year? The cell for July 2013 should be shaded, when 2014 comes, it is no longer shaded, then 2014 becomes shaded, & so on...
View 1 Replies View RelatedI have lot codes in which the first 2 digits represent the year.
9801585623643 would be 1998
01143143143 would be 2001
00103431431343 would be 2000 etc.
I am trying to find out how to create a formula to return the correct year in 4 digit format.
I have tried a few different things combining length, left and year functions but cannot get the appropriate result.