Hi Excel experts council,
Is there an IFS formula that I can use to automatically fill in the values of prorated sales per consecutive calendar year between 2 different start and end dates per product item (per row)?
In the attached dummy, I manually did all calculations to come up with the values in the yellow cells (H9:M17), but realistically when u have 600-1,000+ rows, this is too much time-consuming.
Thanks for your help !
Dany
Here's a general outline of how you can approach this:
Determine the start and end years based on the start and end dates provided.
Calculate the total number of years between the start and end years.
Prorate the sales value for each year based on the total number of years.
Use IF statements or a similar logical function to distribute the prorated sales values across the consecutive calendar years.
For example, if you have the start date in cell A1, end date in cell B1, and sales value in cell C1, you could use formulas similar to the following:
Start Year: =YEAR(A1)
End Year: =YEAR(B1)
Total Years: =End Year - Start Year + 1
Prorated Sales Per Year: =C1 / Total Years
Then, you would distribute this prorated sales value across the consecutive calendar years using IF statements or a similar method.
Without seeing the specific structure of your data, it's challenging to provide a more detailed formula. If you can provide more information about your data layout and structure, I can offer a more tailored solution.
Hi Ashlee,
I like the suggestion and would like to see an example.
On the original post. I attached a sample excel file.
Can u take a look and provide an example of the formula?
Thanks
PS: Is there a way to tweak the formula to offer sales numbers on a total day numbers basis in each year for better accuracy ?
Last edited by Dan2025XLGrandMaster; 03-16-2024 at 08:12 AM.
Since you already have start/end dates, number of days, and daily value, it is simple matter to use the same formula for each year.
It looks like you want to include amounts before 2024 in the column for 2024, so that formula will have to be different than the other years.
See formulas in Solution sheet which give results matching your manual calculations.
Thanks Jeff for your kind reply,
It seems you still had to manually apply a different formula based on the year column.
s there a way to have a one fit-all generic formula ? I was thinking an IFS formula (or anything else).
Something that checks original start and end dates column and will give for instance a 0 number_of_day value in year greater than end date, or earlier than start date?
Then calculate number_of_day in other years where start and end date apply?
In fact, the hardest part (to me) is to assign automatically number of days in each year column with reference to an initial start and end date coverage.
The rest is simple matter of daily cost times that result.
Hope what I said makes some sense and u see where I am going with this.
Dan
The formulas are all the same for all years, except 2024. The reason it is different is that your calculation for 2024 is different than all the other years. So the formula has to be different.
You could contrive a formula that is the same for all years, and have an IF condition checking to see if the year is 2024, but that would be more complicated than what I provided, and with no benefit.
The number of days in each year is not needed for these calculations.
Other than that, I am not sure what you are requesting. How is my solution different than what you need?
Hi Jeff,
Apologies.
When I downloaded your file, I looked it up from my Macbook, which had to convert your formula using a STRIPDURATION function on some year column and not that function on other fuller year duration.
I now downloaded the file on my Dell laptop (Windows 10) and I can confirm the formula is indeed the same, except on column Year 2024.
Fantastic !
I'd appreciate if you could come up with something that transcends the specific case of Year 2024 (knowing well that in the my real life practice, I do have to call the days prior year 1).
However, if u don't have time, it is fine.
Worse case for me: I will remember to have a specific formula for year 1 (when there is a need to back date) and one other for the remaining year !!
Again, Big thank you !!! I will now use that formula straight away on some of my assignments !!!!!
Dan !
Last edited by Dan2025XLGrandMaster; 03-16-2024 at 01:07 PM.
I'm happy to help but I don't understand what you need. Can you be specific about what you mean by "transcends the specific case of Year 2024"? Are you still saying that you would like a single formula that will work for all years including 2024? Is 2024 a special case because it is the first year in the list, or some other reason? I need some kind of specification.Originally Posted by Dan2025XLGrandMaster
All good 6StringJazzer,
You have done the heavy lifting, so the rest is really a small minor detail.
I simply noticed that for column Year 2024, the formula is a little different from Years 2025-2029.
As u mentioned, I guess I can merge the 2 formulas using an IF function condition to check predetermine ranking number and by indexing year 2024 to year 2029 (1,2,3..)
When the formula confirms Year 2024 is Year ''1'', then use the 1st formula, or else use the rest. The idea being having one single formula (maybe long but still) to copy paste everywhere.
Out of curiosity though, is the formula you specifically applied for Year 2024 different because we had some rows with start dates in 2023 ?
If all the items had their start date in 2024, would a single formula work (the one used from year 2025 onwards) ?
Enjoy your day/evening !
Thanks a mill !!!
Dan
Last edited by Dan2025XLGrandMaster; 03-16-2024 at 04:29 PM.
Yes and yes. The reason the formula is different is 2024 is the only year where you want to include data from prior years. If you don't need to include data earlier than 2024, you can use the same formula as the other years.
You are a Champ !!! Thanks !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks