# Incremental Increase By Week When Total For Month Is Known

Apr 9, 2014
I have been a long time lurker of MrExcel, but have always been able to find the answer to my question within the forums. However, I am stumped. I am trying to create a pivot table that will show incremental goals by week, when I have a set goal for the month already. However, I don't want to do a straight average across all the weeks in a month. I would like to see it continually grow during the month. How can i mathmatically do that in Excel?

For example:

Jim had 191 sales leads in the last week of the month in December. Of those 191, he closed 29 of them, for a 15.2% close rate. I want to see him raise that by 6% by next December, so (6%/12 (# months) = .5%). He will need to increase his close rate to 15.7% by the end of January. So, for the month of January, we expect 955 sales leads (5*191) and 149.935 (15.7% of 955) closed. However, I don't want to say Week 1, 2, 3, 4, and 5 in January should all have 29.987 closes, I would like to be able to say week 1 = 25, week 2 = 27 week 3 = 30 week 4 = 32 and week 5 = 34, but have that incremental increase to total 149.935.

There is (sadly) no changing the percents, or the closed leads number. These have already been submitted and aproved. I just have to be able to let people see the goals by week now, but if they want to see the monthly goal, it must equal what has been approved.

View 2 Replies
ADVERTISEMENT
Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

View 10 Replies
View Related
Jun 16, 2014

I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.

I'm using weekday formula but no luck.

View 6 Replies
View Related
Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41

Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

View 3 Replies
View Related
Jan 7, 2010

I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.

Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.

I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.

View 2 Replies
View Related
Jan 21, 2010

I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.

Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?

View 9 Replies
View Related
Jan 22, 2009

I currently have an excel spreadsheet which has monhtly sales totals in it. We have some forumla that compare Year To Date figures with YTD Budgets and we also wish to compare Current Year To Date to Previous Year To Date. The problem I have now is that it currently means updating the range formula for Year to Date Budget and I will also be required to update the Previous Year To Date sales manually too.

Im looking for the spreadsheet to re-calculate theses figures based on the month that they are in. For instance should I be working on the Spreadsheet in January, it will only use the values in the budget sheet from Apr-DEC. Currently I woud be required to change the range in the formual to take into account the extra cell required.

View 5 Replies
View Related
Mar 26, 2008

I have a range of data which is as follows:

Week in month: 1 1 1 5

Site: 01/03 02/03 03/03 etc 30/03 etc

Leeds 10 9 15 20

Manchester 8 5 1 2

Etc

Here's what I need to produce:

March 08 April 08

Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6

Leeds

Manchester

I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.

View 14 Replies
View Related
Aug 14, 2012

I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.

I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))

However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.

View 7 Replies
View Related
Dec 6, 2006

I am trying to write a program that needs to select a different cell everytime it is ran. For example, this month when it runs I need it to select cell BL137. Next time it is ran it will need to select cell BL138. Month after BL139. The cell it selects will always be in the same column (column BL) and the cell number always increases by the same amount (1).

View 6 Replies
View Related
Jun 7, 2014

I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.

So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.

View 2 Replies
View Related
Apr 4, 2009

I would like to create a financial modeling spreadsheet. Basically my problem is this; I want to have a month by month row for 5 years. For the value of each one I would like it to increase by 950 per month automatically.

View 4 Replies
View Related
Aug 22, 2008

I have 22-08-08 in Cell A2 I would like it to change to 22-09-08 on 23-08-08 ideally using edate (but not neccessarily).

Perhaps I can add a formula with conditional formatting eg formula is = "On Today()+1 Add 1 month to cell A2"

View 9 Replies
View Related
Jan 26, 2007

I am trying to add together the total hours for a given week.The spreadsheet is used to show the total hours worked from the signing off and on times given.

CELL C11 CORRECTLY SHOWS THE TOTAL FOR ONE DAY AS 08:30 AND F11 AS 07:30. BUT WHEN THEY ARE TOTALLED IN CELL B13 IS SHOWS 1122:00 INSTEAD OF 16:00.

View 9 Replies
View Related
Nov 2, 2008

1 2 3 4 5 6

11/1 11/2 11/3 11/4 11/5 11/6

sat sun mon tues wed thur

what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week

=$D$1

=$D$1+E5-1

=TEXT(WEEKDAY(D6),"ddd")

cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.

View 3 Replies
View Related
Dec 14, 2013

Please refer to attached sheet.

I am using the attached to schedule the employees. All I want is a formula to add up all schedule hours per week per schedule in cell R2,R3 and R4.

Book2.xlsxâ€Ž

View 2 Replies
View Related
Dec 2, 2013

My spreadsheet is set up so that Column A has dates and Column B has a value. How can I calculate the total number of values for each day of the week? I've tried a few formulas but they either didn't work or didn't actually take the value into consideration and just counted all the 'Mondays'. I'm not sure if that's clear enough, but if we're just looking at Mondays to simplify it:

Monday, 1 January 2000: 2

Monday, 8 January 2000: 5

Monday, 15 January 2000: 0

Mondays: 7

View 1 Replies
View Related
Mar 26, 2008

I want to use a formula to calculate what week number in the month it is (i.e 1 to 6) from a particular date.

I know how to calculate this on an annual basis (i.e. 1 to 52) but not within the month.

This is what I have so far...

=IF(OR(D58=1, D58>=D57), ROUNDUP(DAY(D61)/7,0),ROUNDUP(DAY(D61)/7,0)+1)

d58 is a Weeday formula looking at d61 which is the date i want to look at. d57 is the weekday number of the first day of the month in cell d61.

View 13 Replies
View Related
May 10, 2012

Assuming all my date inputs are in column A, week in B, and month in C.

I found a formula from a forum in converting my date inputs to week number, and here is the formula in column B:

=IF(A40,"WEEK"&INT((A4+1-DATE(YEAR(A4),1,1))/7)+1,"")

- INT(bold) formula part, as those part I just copy paste it, add this and that, and it works

- the reason why I add the "+1" on the very last part is because on my weekly salary payment, every Saturday salary will be paid on the week after.

(ex: 04/28/12 is supposed to be WEEK17, but then I added +1 so it became WEEK18. The main reason is 04/28 is Saturday and like I said I need every Saturday salary to be paid on the week after)

So, I've got the solution to convert my date inputs to Week#, but then I also need this week# to be converted into "MONTH" in a way that the last day/s of any months, but still in the same week of the new month, to be grouped together.

- For an example, 04/28/12 is supposed to be considered as April month, but I need that day to be on the May month.

View 9 Replies
View Related
Mar 12, 2008

what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?

For example (Sunday being the first day of the week):

January 5th 2008 = Week 1 of January

January 6th 2008 = Week 2 of January

February 2nd 2008 = Week 1 of February

February 3rd 2008 = Week 2 of February

WEEKNUM perfectly calculates this, but it is applicable for the whole year.

View 9 Replies
View Related
May 5, 2009

I have the following data by week:

------------------------------------------------------------------------

29-Mar 5-Apr 12-Apr 19-Apr 26-Apr 3-May 10-May

# of work days 4 4 4 4 5 5 5

Manpower 106 85 115 115 120 121 87

------------------------------------------------------------------------

On a second tab, I want to show the data by month:

----------------------------------------------------------------

Apr May Jun

# of workdays 21 (4+4+4+4+5) 21 20

Avg. manpower 108 104 ....

---------------------------------------------------------------

View 9 Replies
View Related
Oct 14, 2007

I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee.

It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.

View 9 Replies
View Related
May 30, 2014

I uploaded an example file.

Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.

The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).

So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).

View 13 Replies
View Related
Nov 15, 2013

I have week numbers from 1 to 52, now i want to get which week number will falls in which month, is there any formula in excel

for eg. Week 01 - 05 will fall in January month (2014), likewise..

View 1 Replies
View Related
Feb 25, 2013

I currently am trying to refine some spreadsheets at work (hospital setting). The type of files im working with are medication sheets where on the left it states the medication and to the right of it, the cells have the days of the month(1-31) but I need them to change depending on the day they come into our facility. Above the numbers i would also like it to say the day of week with the first initial (M, T, W, T, F, S, S) in the cells are the top. It is something that we have to make for each day it it gets really annoying and is a waste of time moving the dates over for every day. find a way where I can open the file and the numbers and letters are all in the right place without having to change it for the day that the patients are coming in.

View 3 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
May 1, 2009

I have 15 projects which are currently been taken, i need to record how much time each staff member takes each week/ per month on the projects, so at the end of week 5 i require a total column, and i require a totals columns at the end of the data.

How can i lay this out cos i'm having trouble visualing the data for per month and per week?

View 9 Replies
View Related
Dec 17, 2009

In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?

View 2 Replies
View Related
Feb 4, 2010

I have using the following Formula: =COUNTA(A3:A7,A10:A14,A17:A21,A24:A28,A31:A35)

Basically is counts how many week days there are in a particular month. Now I have a cell (B47) that counts how many Bank Holidays are in that month. I am having trouble using the formula then minus B47. I must be missing something really simple.

View 3 Replies
View Related
Feb 18, 2009

What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)

View 4 Replies
View Related