sum compared 2 years

**Berna11** · 06-14-2019, 06:01 PM

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.

**GeoffW283** · 06-14-2019, 10:04 PM

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.

**Berna11** · 06-15-2019, 02:48 AM

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

**GeoffW283** · 06-15-2019, 11:52 AM

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.

**Berna11** · 06-15-2019, 12:16 PM

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

**GeoffW283** · 06-15-2019, 01:28 PM

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.

**Berna11** · 06-16-2019, 05:08 AM

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.

**GeoffW283** · 06-16-2019, 09:02 PM

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:

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.
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.
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"
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.
```
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
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.

**Berna11** · 06-17-2019, 05:19 AM

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

**GeoffW283** · 06-17-2019, 06:48 PM

"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:

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.
```
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.
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.
In M125:
Formula:
```
Please Login or Register  to view this content.
```
Then add column M formulas in a similar manner to col-E (I went only as far as April)
Format column-M as "d"
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

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

See the attached workbook.

**Berna11** · 06-18-2019, 04:15 PM

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

**GeoffW283** · 06-20-2019, 01:35 PM

OK - back to col-Z on foglio3 - no problem.

Here is yet another attempt at the N122, N522 formula:

Formula:

Please Login or Register  to view this content.

This formula provides the expected results for all examples provided so far:
Post #7: L120=2016, F123=2/28/2016 => N122 = 5600, N522 = 0
Post #7: L120=2016, F123=2/29/2016 => N122 = 5600, N522 = 6100
Post #7: L120=2017, F123=2/28/2017 => N122 = 4900, N522 = 6123
Post #9: L120=2018, F123=1/04/2018 => N122 = 1400, N522 = 0
Post #11: L120=2016, F123=2/28/2016 => N122 = 5600, N522 = 0 (dup)
Post #11: L120=2017, F123=3/01/2017 => N122 = 4900, N522 = 12223

See the attached workbook. Sorry this is taking so many iterations.

**Berna11** · 06-21-2019, 06:00 AM

hello, I saw the new file, from some tests I find these errors:

L120=2016, F123=2/28/2016 => N122 = 5600, N522 = 0
but in N472=5400 should be = 39900

L120=2020, F123=3/01/2020 => N472 = 5600, should be = 41300

sorry again for the inconvenience
Thank you

**GeoffW283** · 06-21-2019, 01:20 PM

I'm sorry for the number of tries this is taking.

I have made a minor change to the N122/N472/N522 formula highlighted in red:
=IF($F$123 < F124, 0, IF($F$123 >= F125, SUMIFS . . .

That fixes the two issues you note in post-13.

I also re-verified all previous test cases with this formula change. Here's a summary of all test cases to date. Where there is a "g-" prefix to the expected result that is a value that I provided but to the best of my understanding is correct.

expectedResults.png

Revised workbook is attached.

**Berna11** · 06-22-2019, 03:58 AM

hello in the meantime thanks

EDIT:

F123 01/26/2024 in N272 = 7200 should be = 12500

F123 03/01/2024 in N522 = 17700 should be = 30000

**GeoffW283** · 06-23-2019, 12:42 AM

OK, I have a simplified formula for N122 and copied down:

Formula:

Please Login or Register  to view this content.

It passes all expected results from previous posts and for the first time I'm somewhat confident that this will work for all cases. Prove me wrong

See attached file.

**Berna11** · 06-23-2019, 10:36 AM

hank you very much,
from the first checks it seems to be the right time.
I still do other checks and I'll let you know tomorrow.
I am sorry that the problem was more complicated than expected.

Thank you

**Berna11** · 06-27-2019, 01:42 AM

Hi, from the controls made it all seems fine.
So I put the solved if some error comes out I'll let you know
Thanks again

**GeoffW283** · 06-27-2019, 12:29 PM

Good news! Thanks for the feedback!

sum compared 2 years

LinkBack

Thread Tools

Rate This Thread

Display

sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Re: sum compared 2 years

Thread Information

Users Browsing this Thread

Similar Threads

Adding a Sequence of Consecutive Years in Between a Range of years

[SOLVED] How to Format Dates to Colors, That Are Over 1 Year, 2 Years, and 3+ Years

[SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.

[SOLVED] Best chart for comparing historic data of 3 years sales along with 3 years shoppers num.

Extracting individual years from a range of years in Excel 2007

I need help in calculating years worked plus age to get a result of years months and

[SOLVED] [SOLVED] Overlay 4 years of data as a line on 4 years of columns for several x catego

Bookmarks

Bookmarks

Posting Permissions