I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....
View 9 Repliesi currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
How can I do an average in Excel 2003 when it's not a range but I also don't want to count a certain cell when there's no data or zero? I have attached the spreadsheet.
View 6 Replies View RelatedI have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.
I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).
Is it possible to do this?
I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.
Is there a way to convert data that has dates to week by week? such was week 1 week 2 week 3 etc....
View 8 Replies View RelatedIs it possible for a workbook to delete itself after two weeks or by date since putting in VBA codes?
Or does it have to be active to be able to delete itself.
I'll be going away for a holiday in two weeks (yepeee) and the person who's taking over my place will only use it on first day of my absent.
I do not want others to use my file (of course I'll back it up before I'll go).
I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
Here´s the deal: based on weekly reports, I need to count and sum orders created in our CRM.
I´m exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:
1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD
I don´t need help with the last one, that´s the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.
So, my questions are...
1. I need to set different formulas for count and sum, that´s clear, but how can I make this autimatically without having to change the formula each week?
2. Do I need to consider the date my main file is modified, and count backwards?
As usual I am not sure I´m being clear, though I hope I´m getting better at this. I´m attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.
The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.
I have a calendar. All months across the top, formatted as date mmm with content 1/2 2/1 3/1 4/1, etc. so the cells display Jan Feb Mar Apr, etc. All days down the side formatted as number displaying 1, 2, 3, 4, etc. All the days of the year are formatted as time h:mm.
I want to create a cell that shows me the total time for the last 14 days.
I need a formula that will tell me how many weeks there are in a month.
If cell A2 had the month and cell B2 had the year then I would like to return 4.....
I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)
A second thought was to copy paste them to a new sheet for each seperated week.
and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week
i thought maybe working with boxname?
"Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).
I need to turn that date into the week that it falls into within a particular quarter of a year.
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).
And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).
I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.
For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:
52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks
I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.
how create a formula in D2 to D27 using the date A1 = (A2 to A27), and calculate how many weeks delayed comparing the value of Cum Actual (B1) if it less than, equal or greater than the value of Cum Planned (C1).
Sheet1
ABCDE1Week26-Jul-09Cum ActualCum PlannedDelayed2105-Apr-09 - 83.27
3212-Apr-09 - 237.00
4319-Apr-09 - 416.36
5426-Apr-09 - 570.09
6503-May-09 - 672.58
7610-May-09 - 826.31
8717-May-09 667.16 1,005.67
9824-May-09 667.16 1,244.25
10931-May-09 667.16 1,431.81
111007-Jun-09 667.16 1,646.79
121114-Jun-09 908.16 1,831.06
131221-Jun-09 1,345.16 2,046.04 ..............................
I have a weekly forecast for what will be sold for the upcoming year and want to sum the quantity by months. For example, 1/6/07 sell 351 units, 1/13/07 sell 315, 1/20/07 sell 1,165, and 1/27/07 sell 328 units so Jan would return 2,159. To avoid future user error, I'd like to sum by month without the basic sum( range) formula and have tried the following SUMIF statement:
=SUMIF(Weeks,"=Jan*",Widget_Qty)
That returned zero. I formated my Weeks to look like "6-Jan-07" so I thought that the month name could be used as a text but this was also wrong. I also tried changing the month value from text, i.e. Jan to a date value displayed as a "mmm" but failed again.
I'm trying to average data for the past 3 years. My spreadsheet is setup like this.
Year, NameID, Salary, Average Salary
I have the spreadsheet sorted by NameID so most people in the database will have 3 entries right after another. How do I determine an average salary for the person across all 3 years?
I need the average salary to display across from the most recent date if that is possible as well i.e. if the latest salary date is from 2006, I would prefer that is entered into the row corresponding to 2006 and leave the 2004/2005 rows blank.
Also, some people might only have 2 years of data so keep that in mind as well.
Column A has approximately 50,000 rows with unique property parcel numbers. Column B has numbers that represent an elevation point on the parcel. The parcels are not level. The elevation of a parcel varies depending where one measures. So,...I have many rows with the same parcel number in column A but the adjacent column B shows a different elevation. Is there any way to:
1) remove all of the duplicate column A parcel number rows so I have just one column A parcel number row.
2) get the average of all the column B elevations to show up on the same one row?
I should only have approximately 15,000 rows if I could get rid of the duplicates and get the average elevation. I have version 2007 but I have to share with others who have version 2003. I don't know if that makes a difference but I thought I should mention it
I have received a task to do for school and I tried my heart out but I can't seem to figure this out. It's really simple but I can't seem to do it... I'm not very good with excel.
The excel file is attached .....
I have a spread sheet and it has a column in it with a drop down menu. The words in the menu are Hot, Medium and Cold. I want to have it tell me which one if showing up the most.
View 13 Replies View RelatedThe organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).
One “reporting group” has many cost centres.
There are several reporting groups.
I need to report on average salary per grade / per reporting group.
I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.
In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.
I have a column of data in Excel in the [h].mm.ss format. These values range from a few minutes to hundreds of hours. I need to get the average of these times.
My code is below. I'm getting a type mismatch error on the line that tries to add the time to the total. The line is in red. I've tried using a Integer, Long, Double, and Date as variable types for this particular variable. All with the same result.
I need some help with a formula. I've tried daverage, cant seem to get it to work. below is an example of the spreadsheet.
A B C D E
Date Name Project Score Pass?
5/1 Joe Test 55% No
5/1 Jan Quiz 88% Yes
5/4 Jon Test 100% Yes
5/9 Gary Test 75% Yes
5/11 Joe Quiz 90% Yes
5/18 Mary Test 45% No
Ok, I need a formula that I can execute from a different worksheet than the data is on. I need the formula to give me the average of the scores in column D for the criteria in Column C "Test". I tried daverage=(A1:E7, "Project", C1:C2) I cant get it to work. Do you guys know any other way to do this?
in Sheet1, i have 'part number' listed in column A and 'customer number' in column H. in column P, i have 'customer price' (for that part). so there are multiples of the same part numbers listed in column A.
in Sheet2, i have a summary showing totals for the parts (regardless of the customer), which includes SUMIFs for other columns such as pieces sold, etc. what i am looking to do is get the 'customer price' average by part (in column P/worksheet1) for each part on Sheet2.
Sheet1 sample:
Column A / Column H / Column P
31397 / 1001240 / $6.60
31397 / 1020312 / $6.65
31397 / 40020 / $6.63
31832 / 1047493 / $4.22
31832 / 1035195 / $4.22
31832 / 40017 / $4.40
So for Sheet2 i would like it to be like:
Column A / Column B
31397 / $6.6267
31832 / $4.28
i also need this formula to work if column P has any cells with zero entered.
I'm making up a list of average scores across a season. I have an average column and I need to be able to deduct 10% from this average, then have excel ignore any scores below this new number and make up a new average (kinda like a handicap score).
I have columns for average (BD), 10% of this (BE) "ignore any score lower than"(BF) but Im not sure how to get excel to add up only the scores below the number in BF and make a new average column of only these in BG -
Last column would be to give each person a classification based on cutoffs (for instance 1-10=C Class, 11-20=B Class, 21-30=A Class etc etc). Is there a way to make "if BF=xxxx, make BH A, B, C etc?
I need to average out a set of numbers but the amount of numbers inputed will vary, these number can also be either positive numbers or negative numbers but will never be 0.
How do i do this and I would like this in formula terms?
Say cells a3,b3,c3 were the numbers that I want averaged but there is not always a number inputted in each of these cells. I want the answer to show up in cell a4
i've got a worksheet with 3 columns each declared, called date, place and avspeed
I'm trying to average the avspeed if it falls under certain criteria within the other 2 columns.
The dates are in day format and basically I require the average over the week
If tried using
=AVERAGE(IF(WEEKNUM(date)=45,IF(local=I4,avspeed)))
also declaring it as an array formula but both
also tried the following by summing and then counting the data and dividing one by the other, but so far neither are working
Trying to compute annual change I've always used the formula "(b1-a1)/a1" - where b1 is the current year, a1 is the prior year. I've been asked to compute average annual change over a number of years and thought simply by using the above for each year and then taking the average of all these, I'd be ok. I recently found a formula as follows: "(y2/y1)^(1/n)-1", where y2 is current year, y1 is 1st year I have data, n = the number of changes.
View 7 Replies View RelatedI have a spreadsheet,3 columns are shown in the attached.The first column gives the date of the event,the second column the time and the third a rating.I want to put the average rating for each event in each cell in column 4.
View 9 Replies View RelatedI have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
Trying to think of a formula that will tell me the number weeks stock I have,would like to put the formula in h6 and copy across giving me the weeks stock
Green line is sales, yellow balance stock
EX1.JPG