+ Reply to Thread
Results 1 to 11 of 11

sum compared 2 years

  1. #1
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    564

    sum compared 2 years

    Good evening, I need your help, I got stuck comparing the sum of 2 years.
    In the attached file I have some lines that I don't need for the request and to make the file more readable
    In the Sales Volune sheet I have the months (I put only 3) and 2 years the current one on the left and the previous year on the right.

    I would like to compare the H-I column values with those of the N-O column of the Sales sheet.

    In Sheet 3 in column F-G I created the dates of beginning weekend for current year F and previous year column G
    in F123 I entered the date to stop the calculation.

    In H122 and below in the other weeks there is the formula that works correctly for the current year.

    What I need is the formula in N122 similar to the one in H122 that does the same calculation but for the column N Sales Volume sheet and relative to the previous year.

    The year changes to L120 Sales sheet.

    I hope I explained to you if it is not clear, just ask
    thank you.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    859

    Re: sum compared 2 years

    Should there be an equivalent to "Sales Volume" column-E (which is a 2016 date column) for the 2015 year ?? It looks like your N122 formula would need it.

  3. #3
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    564

    Re: sum compared 2 years

    Hi, thank you very much for your reply.
    For the year 2015 I thought we could take 2016 and put -1.

    To make things easier in sheet 3 column Z I put the days
    that refer to the previous year 2015.
    For other clarifications I am here.
    Thanks again
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    859

    Re: sum compared 2 years

    Here is my proposed N122 formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I started with the H122 formula. All I changed was the column to be summed from H to N and used your newly added col-Z for 2015 dates. The result is 5,600. Is that what you expect? If not then please give us your expected result and how you arrive at it.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  5. #5
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    564

    Re: sum compared 2 years

    ye thank you
    maybe you forgot a basic thing in $G$123 what did you put in?
    ...."<="&$G$123

    would be the block date for 2015 that made me go into confusion ...
    Leap year and day difference between current year and previous year
    must be taken into account.
    That 5.600 for that week is correct

  6. #6
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    859

    Re: sum compared 2 years

    Yes, sorry, I forgot to mention that I had copied F123 to G123 which was previously blank and which resulted in an answer of 0.

    So I think you are saying that the N122 formula that yields 5600 is correct, but that there is still a problem somewhere with leap years. Can you provide a specific example cell where a problem arises and what your expected result for that cell.

  7. #7
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    564

    Re: sum compared 2 years

    Hello
    if in G123 a date is set, in my opinion, it must be related to F123 but with a previous year.

    Some examples:
    With year Sales L120 = 2016 and in cell F123 = 02/28/2016
    in week 9
    in cell H522 = 0 correct
    in cell N522 = 44800 must be 0.

    With F123 = 02/29/2016
    in H522 = 1000 corrected
    in N522 = 44800 it must be = 6100 on the Monday of the same week but of the previous year.

    With year in Sales L120 = 2017
    date in F123 = 02/28/2017
    in H522 = 117 corrected
    in N522 = 37500 must be = 6000
    sum of Sales N253 (which is empty) + N325.

    In short, if in H I do the Monday + Tuesday I have to sum up the same Monday and Tuesday of column N, darkness account of a leap year that jumps forward 2 days instead of one.
    I hope it is clear.

  8. #8
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    859

    Re: sum compared 2 years

    I am attaching my updated version of your post #3 file. It produces your expected results for the three examples you provide in post #7. Despite this I am still far from certain that I have a correct approach and have not simply tuned the output to meet your specific examples. Please test thoroughly and let me know what does not still work.

    Some notes:
    1. Prior year date Col-Z that you added for me was not populated for all rows and now that I understand a little better what you are doing I have eliminated use of this column which I think would be your preference anyway.
    2. I set the language selector cell SalesVolume!Q5 to "Italiano" as your formula in SalesVolume!M:M then provides real dates. Setting the language to English simply gives day text in col-M. I imagine the m:m formula needs to be fixed to behave the same regardless of what language is selected but I did not pursue that yet. I also reformatted SalesVolume!M:M so I could better see the full dates - you can restore your desired formatting of course.
    3. For convenience I have added SalesVolume!M120 and set it to 1 if L120 is a leap year and 0 otherwise. For clarity I have named this cell "isLeapYear". The M120 formula is (Microsoft recommended!):
      Formula: copy to clipboard
      Please Login or Register  to view this content.
      I made a similar change in cell S120 to indicate whether the "previous" year was a leap year and named that cell "lastIsLeapYear"
    4. Again, as I understand a little better what you are doing, then I think my adding foglio3!G123 was unnecessary and just created confusion. I have eliminated that cell and adjusted the col-N formula accordingly. In N122 and copied to N522:
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    5. Purely so I could see more easily what was happening I have reformatted foglio3!F124:G125 and foglio3!F524:G525 dates so I could see more clearly what was happening
    6. I changed SalesVolume!N253 from 0 to 123 for testing purposes so that I could be more sure that I was picking up the right cell.


    With the above changes I believe I meet your expected results for all three test cases. However, as stated at the top of this post I'm still far from confident that this represents a full and correct solution. Please test and let me know what other issues you find.

    The attached workbook implements the above comments.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    564

    Re: sum compared 2 years

    Thank you so much for the hard work and your commitment
    As for the Z column it was not complete it was for proof so much the problems occur between February March.
    I have seen the variations on the file for the column M Sales if you find the solution also to fix it in English well, otherwise we return to Z sheet3.
    For the results, the examples above are correct but do not come back if the year is not a leap year

    Example L120 2018
    date F123 04/01/2018
    as a result 900 must be = 1400

    over all the weeks to follow in N from one day less.

    I tried to put +1 in the formula in S120 Sales
    and it's fine for 2018 but busts out on 2016.

    I'm trying to create a date that shows F123 a year before taking into account the leap let's see what comes out.

    Thanks again

  10. #10
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    859

    Re: sum compared 2 years

    "I have seen the variations on the file for the column M Sales if you find the solution also to fix it in English well, otherwise we return to Z sheet3."
    I think I have fixed the language issue as follows:
    1. For SalesVolume!F:F - in F125 copied down (I went as far as April - not sure why you explicitly have na() below that.
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    2. The parts of the formula in square brackets sets the language. Note that this columns is now "text", not a real date. That's OK as col-E is still a real date.
    3. Add new column SalesVolume!N:N instead of "col-Z". This keeps the previous year columns symmetrical with the current year columns (if you really don't want this then, yes, we can restore col-Z). In N125 copy the F125 formula and copy down.
    4. In M125:
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    5. Then add column M formulas in a similar manner to col-E (I went only as far as April)
    6. Format column-M as "d"
    7. There is another language setting cell on foglio3 at cell N22. I have linked it to SalesVolume!R5

    Now on to calculation correctness:

    "Example: L120=2018, date F123="04/01/2018" result is 900 should be 1400"
    First, by 04/01/2018 I think you mean 04-January-2018.

    Here is an adjusted formula for N122 and N522 that generates the expected 1400 for this example and still works with the three examples previously provided. I'm still not totally confident that it will cover all possible cases though:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks for the rep - very generous for a partial solution at best

    Let me know how your testing goes with this latest version.

    See the attached workbook.
    Attached Files Attached Files
    Last edited by GeoffW283; 06-17-2019 at 06:52 PM.

  11. #11
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    564

    Re: sum compared 2 years

    hi, sorry for the delays.
    I get upset but we have moved away from the solution.
    Your proposal to edit Sales sheet and make it symmetrical is not bad unfortunately I can't change the Sales sheet for this reason I opted for the Z column otherwise I would have put it in N-M too.

    I modified the formula taking up the dates in Z but the results do not return.
    Example
    F123 02/28/2016 in N122 = 500 should be = 5600
    The other weeks also give the wrong result

    With date 03/01/2017 in N122 = 3000 it should be 4900.
    The other weeks also give the wrong result

    This is the formula I modified:

    Please Login or Register  to view this content.
    I'm sorry to still give you boredom, it's a bit complicated.
    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1