Dear friends, I need help again!

:(I receive data on fishing on a weekly basis which I save as follows:

Column A: "the number of the week"

Column B: "Month of the year"

Column C: "Year"

Column D: "the tons of landings":A B C D 53 12 2009 450 1 1 2010 300 2 1 2010 350 3 1 2010 450 4 1 2010 550 5 1 2010 500At the end of each month I need to calculate the total landings on a monthly basis, i.e. January, February etc

Let's assume the weeks indicated above refer to week 53 of 2009 and weeks 1,2,3,4 and 5 of 2010.

That means that 4 days of week 53 are in December 2009 and 3 in January 2010.What I need is therefore a formula that adds up the fishing by month taking into account the values for the week split between 2 different months proportionally to the number of days in each month (i.e. for the example above 4 days for Dic and 3 for Jan)

What I'm thinking is to add 4 columns as follows:

A B C D E F G H 53 12 2009 450 4 3 257.1 192.9 1 1 2010 3000 0 300 300 2 1 2010 3500 0 350 350 3 1 2010 4500 0 450 450 4 1 2010 5500 0 550 550 5 1 2010 5000 0 500 500where columns E and F identify the number of days belonging to each month for those weeks split between 2 different months, and columns G and H claculate the amount of fishing for the 2 parts of the week.

With this I'm sure there's a way to sum the values for each month jumping from column H to G when necessary.I have been trying for weeks to come up with a solution but my limited knowledge of excel formulas has taken me nowhere.

:(My time series goes from 1993 to the date.

If anybody could help I'd be really grateful!

Regards

Hi, You could use the excel function sumif. The link attached is a picture

of how you can do it.

Solution

Hi Candelyn, thanks for your suggestions.

Unfortunately your solution is only a partial solution.

My big problem is that those weeks split over two different months (e.g. week 53 in 2009, which has 4 days in 2009 and 3 days in 2010) are not accurately accounted for in your formula.

In your solution in fact 450 tons went to Dec 2009.

In reality, if you look at my second table you see that I calculated that 257.1 tons belonged to Dec 2009 (i.e. (450/7)*4) and only 192.9 tons to Jan 2010 (i.e. (450/7)*3).The formula I need is a formula that is able to add the amount of fishing belonging to each month when you have a split week.

I hope it is clearer now.

Thanks for your help!

Hi, As there is no function to convert a week number back to it's date range, it is hard to write a formula to achieve the required split.

I have written a custom function that will return the percentage of a week number that falls in a particular month in a particular year.

The function is called WEEKSPLIT() and requires two arguments:

WEEKSPLIT(weeknumber, monthdate)weeknumber is an integer between 1 and 53 (the week number)

monthdate is a full Excel date for the month in question. It must be in the correct year as the way week numbers relate to dates varies from year to year. The monthdate can actually be any day in the month in question.Here is what happens:

with dates in column A, week numbers in column B and the formula: WEEKSPLIT(B1, A1) in column C.

Column A has been formatted to show Month and Year only

Column C has been formatted to show 2 decimal places, but the actual percentage is returned to a higher degree of accuracy.Here are some sample week numbers, some falling fully within the month and some straddling two months:

A B C Jan/09 1 42.86 Jan/09 2 100.00 Mar/09 13 100.00 Mar/09 14 42.86 Apr/09 14 57.14 Apr/09 15 100.00 Apr/09 18 71.43 May/09 18 28.57

You will see that each 'pair' that straddles two months, adds up to 100(%)

If May 2008 is used with week 18, the result is 42.86, as the week numbers fall differently in different years.If the week number passed to the function does not fall within the month of the date passed to the function, the function returns the #NA error.

If the week number is less than 1 or greater than 53, the function returns the #NUM error.

Other errors result in the #VALUE error.You should be able to use this function to get the results you need. It acts like a normal Excel function and can be combined with other functions in formulas, as required.

To install the function, right-click the name tab at the bottom of the worksheet and select 'View Code'

This opens the Visual basic window.

In the Project Explorer pane on the left, the worksheet name will be selected.

If the Project Explorer pane is not visible, from the Visual Basic menu bar, select View - Project Explorer.

Right-click the worksheet name or the workbook name and select Insert - Module (not Class module)

Double click the new module (Module1)

In the large Visual Basic window paste the following:Option Explicit Public Function WEEKSPLIT(wknum As Integer, mnthserial As Date) As Variant 'returns percentage of days in the specified week that fall 'in the specified month 'the serial number for the month must contain the correct year 'as week numbers cover different dates in different years. Dim dblMnthFst As Double Dim dblMnthLst As Double Dim dblStart As Double Dim dblEnd As Double Dim blnStart As Boolean Dim blnEnd As Boolean Dim n As Double 'test week number - must be 1 to 53 'if not error out If wknum < 1 Or wknum > 53 Then WEEKSPLIT = CVErr(xlErrNum): Exit Function 'create first day of month dblMnthFst = DateSerial(Year(mnthserial), Month(mnthserial), 1) 'create last day of month (+1 month - 1 day) dblMnthLst = DateAdd("m", 1, dblMnthFst) - 1 'set start & end date found flags to false blnStart = False blnEnd = False 'find first day in month with same week number as requested 'and then find end date of week number requested For n = dblMnthFst To dblMnthLst If DatePart("ww", n) = wknum And blnStart = False Then 'week number found (for first time) 'record start date dblStart = n 'flag found start blnStart = True End If If DatePart("ww", n) > wknum Then 'now in next week - so save yesterday as end date dblEnd = n - 1 'flag end found blnEnd = True End If If blnEnd Then Exit For Next n 'if start of week not found - error out If blnStart = False Then WEEKSPLIT = CVErr(xlErrNA): Exit Function 'if end of month reached without end date flagged 'set end date to end of month If blnEnd = False Then dblEnd = dblMnthLst End If 'calculate percentage of days in week in this month WEEKSPLIT = (dblEnd - dblStart + 1) / 7 * 100 End Function

From the Visual Basic menu select Save

Use Alt+f11 (The Alt key and function key number 11) to return to the main Excel window.As a test enter this in cells A1, B1 and C1

A1 29/Mar/2009

B1 =WEEKNUM(A1)

C1 =WEEKSPLIT(B1,A1)

B1 will show 14 and C1 should show 42.8571 (depending on number format)

Hope this helps.Regards

Hi, I note that you have different tonnages for week 53 in 2009 and week 1 in 2010.

These two weeks are effectively part of the same week as this shows:

Saturday 26/12/09 52 Sunday 27/12/09 53 71.43 Monday 28/12/09 53 Tuesday 29/12/09 53 Wednesday 30/12/09 53 Thursday 31/12/09 53 Friday 01/01/10 1 28.57 Saturday 02/01/10 1 Sunday 03/01/10 2 100

71.43 + 28.57 = 100%In the custom function I used the MS default for weeks with Sunday as the first day. This can be changed if Sunday is not treated as the first day of the week.

The Visual Basic Help file for the DatePart function shows options for week numbering.

Regards

Hi, I was able to get the data by months, but I had to change my user defined function to return zero percent when the week number was not in the month in question, rather than the #NA error.

Change the code for the function as follows:

'if start of week not found - return zero as the percentage 'If blnStart = False Then WEEKSPLIT = CVErr(xlErrNA): Exit Function If blnStart = False Then WEEKSPLIT = 0: Exit Function

You will see the line that I commented out - you can actually delete it, and add the revised line. The text describing the line has also been changed.

-Assuming you have a year's worth of data with Week in column A, Month in column B, Year in column C and Tons in column D.

Row 1 contains headings.

Row 2 is week 53 of prior year and row 3 is first week of current year

The Tons data is the same in D2 and D3 as they are part of the same week.

For this example the current year is 2009.Row 55 will contain the tonnage for week 53 of the current year.

Add two columns E & F

E1 contains "Split" and F1 contains "Sub-total"Enter this formula in E2: =weeksplit(A2,DATE(C2,B2,1))

Drag this down to extend the formula to row 55

Cell E55 will contain: =weeksplit(A55,DATE(C55,B55,1))Leave cell F2 empty

In cell F3 enter: =IF(E2<>100,(weeksplit(A2,DATE(C3,B3,1))*D2/100)+D3,D3*E3/100)

Drag this down to extend the formula to row 55

Cell F55 will contain: =IF(E54<>100,(weeksplit(A54,DATE(C55,B55,1))*D54/100)+D55,D55*E55/100)Now create a table of months.

In cell G1 enter: 01/Jan/2009

In cell H1 enter: =EDATE(G1,1)

H1 will show 01/Feb/2009 (depending on formatting)

Drag the formula in cell H1 to extend it to column R

R1 will show 01/Dec/2009

Select G1 to R1 and format as "mmm/yy"In cell G2 enter this formula: =SUMPRODUCT(($B$3:$B$55=MONTH(G1))*($F$3:$F$55))

Note the $ signs - they are required to ensure that the formula works when extended.

Select G2 and drag to extend the formula to column R

Column R will contain: =SUMPRODUCT(($B$3:$B$55=MONTH(R1))*($F$3:$F$55))Cells G2 to R2 will now contain the monthly totals with weeks split between months.

To confirm the results:

In cell S2 enter: =SUM(G2:R2)

In cell D56 enter: =SUM(D3:D54)+(D2/100*E3)+(D55/100*E55)

Cells S2 and D56 should show the same totalThe key to the process is column F which recognizes when a week in the row above is not 100% and therefore the first week in the next month must also contain part of the tonnage from the previous week number. It adds the first full week to the previous partial week, using the

currentmonths date to calculate the split.

The previous line has used thepreviousmonth to calculate the split.The SUMPRODUCT formula only adds column F subtotals when the month from the date in row 1 above it matches the month in column B. SUMPRODUCT is effectively an array formula and repeats the calculation for each row in the ranges given to it, and adds each row's calculation to the total as it goes along. When the month in row 1 does not equal the month in any row in column B, the result is zero, hence that row's total is always zero whatever is in column F on that row as anything x zero is zero.

You will note that the formula in column F uses WORKSPLIT and has to create a valid Excel date for the function. It creates the date with the DATE() function which takes the arguments, year, month and day.

As WORKSPLIT() is only interested in month and year, the day is always set to 1, - DATE(C3,B3,1).Hope this helps.

Regards

Dear Humar, thanks a lot for the work you put on this.

Much appreciated!Unfortunately it's not working. In particular the formulas

=weeksplit(A2,DATE(C2,B2,1))=IF(E2<>100,(weeksplit(A2,DATE(C3,B3,1)*D2/100)+D3,D3*E3/100))

=SUMPRODUCT(($B$3:$B$55=MONTH(G1))*($F$3:$F$55))

seem to have problems.The first one appears unable to distinguish between a change of month with a split week (i.e.Dec 09/Jan 10) and a change of month without a split (i.e. Jan 10/Feb 10).

The second formula gives #NAME? in F3, #NUM! in F4 and F5, FALSE in F6, F7, F8, and #N/A in F9 corresponding to #N/A in E8 etc...

The third obviously is affected by the results produced by the second.

I'm not sure why is this as I've copied everything as you suggested.

Three things.First, what is the reason for inserting the visual basic programming?

Second, the way you worked seems to rule out the possibility of developing any formula for the data relative to the split week on cells on the same row rather than on two different rows (i.e. D2 and D3 rather than D2 and E2).

Third, how can I post an excel tab so that you can see what I'm actually doing?

Thanks again for your help!

Humar, one other thing.

Why every time a press enter in the spreadsheet I'm working on I now get a message saying "Ambiguous name detected: WEEKSPLI"?

Thanks!

Hi, I used Visual Basic because I could not see any existing Excel formula that allows you to convert a week number to it's dates.

The WEEKSPLIT() provides the percentage of the week in different months, based on the week number and a date which provides month and year.

There may be a way to achieve what you want without using a custom defined formula, but I can't immediately see how to do it using your source data.

If your data included full dates for the start of each week rather than month numbers and year numbers, with tonnage for each week based on the first day of that week, then it would be possible.

As the first day (date) of each week number changes from year to year, it is not possible to work back to find the start date of each week, using existing Excel functions when the date of the start of the week is not explicitly stated.Regarding the split:

The week 53 / week 1 situation is slightly different in that the two parts (week 53 in December of one year and week 1 in January of the following year) make up a single week.The first thing to do is to be sure that the WEEKSPLIT function is working as intended.

This is what I get:

A B C 1 01-Mar-09 01-Apr-09 2 14 42.86 57.14 3 15 0.00 100.00B1 is 01-Mar-2009

C1 is 01-Apr-2009

A2 is 14

A3 is 15

The formula in B2 is: =weeksplit($A2,B$1)

Drag it across then down to fill the four cells

You should see the four percentages as shown above.

Note that you get different results for the same week number when different months are used.You said

The first one appears unable to distinguish between a change of month with a split week (i.e.Dec 09/Jan 10) and a change of month without a split (i.e. Jan 10/Feb 10).

The above matrix shows that the formula does distinguish a change of month with a split week (42.86 & 57.14 for week 14 in March 09 and April 09 respectively).I am not sure what you mean by a change of month without a split. If a week is not split it is either 100% if within the month or 0% if it is not in the month in question.

Week 15 above is fully within April 09, so it shows 100%. For any other month it will return zero.As I mentioned before, the change from week 53 of one year to week 1 of the next year is really two parts of the same week. Week 53 in December 08 plus Week 1 in January 09 will add up to 100%

You also said

Second, the way you worked seems to rule out the possibility of developing any formula for the data relative to the split week on cells on the same row rather than on two different rows (i.e. D2 and D3 rather than D2 and E2).The formula does not distinguish between weeks in columns or in rows. The following shows the same week 14 and week 15 data in opposite orientation:

4 A B C 5 14 15 6 01-Mar-09 42.86 0.00 7 01-Apr-09 57.14 100.00The formula in cell B6 is:

=weeksplit(B$5,$A6)

Drag it to fill the four cells, and you get the same results as before.At this stage there is no point in discussing formulas that rely on WEEKSPLIT() until you are comfortable that you have the function working as designed.

Please make sure you use the revised formula based on the change given in response number 5

Change the code for the function as follows:

'if start of week not found - return zero as the percentage 'If blnStart = False Then WEEKSPLIT = CVErr(xlErrNA): Exit Function If blnStart = False Then WEEKSPLIT = 0: Exit Function

the line commented out can be deleted (the one containing xlErrNA)If you send me a private message with your e-mail address (do not post your e-mail in a forum post), I will send you the sample worksheet with the monthly formulas etc.

Regards

Hi, "Ambiguous name detected: WEEKSPLIT" occurs because you have the code for the WORKSPLIT() function, more than once.

Did you copy the workbook and now have both copies open?

Regards

Hi Humar thanks for sending me the stuff.

I'll let you know how it goes.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History