# sum compared 2 years

1. ## 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.

2. ## 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. ## Re: sum compared 2 years

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

4. ## Re: sum compared 2 years

Here is my proposed N122 formula:
Formula:
`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.

5. ## 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. ## 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. ## 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. ## 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:
`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:
`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.

9. ## 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. ## 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:
`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:
`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:
`Please Login or Register  to view this content.`

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

See the attached workbook.

11. ## 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

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

#### 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