• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Forecast mixcing month and year

kinkart

Member
Hi,
I'd like to distribute a value across month and year: the next 2 years are monthly distributed, the years after are yearly.
Attached is what i meant + a few scenarios.

cells in ligh yellow are manula input, everything else is (should) be calculated.

I've tried a few things, but my brain fried... Can someone help me out please?
 

Attachments

vletm

Excel Ninja
kinkart
Is there any reason that You would like to use Your Month and Years as You have done?
It would be more useful to use those as dates.
 

kinkart

Member
thank you vletm!
this works really well. But since I'm a complete newbie with macros, do you know if the same result could be achieved with formulas?
 

vletm

Excel Ninja
kinkart
You could use that my file then that should work ... if Your real data/layout is as with Your given sample file.
You no need to know macros to use it -- fill /modify values...
Sure, it would be possible to get almost same results, but then You should take care more - eg range.
... as well as ... take care that none would delete any formulas.
 

kinkart

Member
I simplified the layout in my example file, the original file is a lot bigger and complex, with other macros.
If something similar could be achieved with formula, would be better for my org
 

vletm

Excel Ninja
kinkart
Yes ... many times 'simplified' sample make more challenges for You.
What is complex?
If there are other macros ... then one more won't be a challenge
... or of course, it would need to take care, what do others do?
Do You really want to have ... a lot of formulas, instead eg my sample code?
Of course, it's Your choice.
... even with formula-solution, those would need to know used layout.
 

kinkart

Member
complex as in multiple tabs, managed by multiple people (editors) and used by 100s of users.
I guess for most of the experts on chandoo it wouldn't be considered complex, but it is for us :)
So using formula that most editors understand (vs VB) would be beneficial.

One thing I notice on your macro: when the "To" field is a month (ie 202111) and the "from" field is a year (ie 2023), it doesn't distribute correctly.
For example, if we had 1000 to distribute, it should show 1000/(2+12) per month, so 71.42 for 202111, 71.42 for 202112 and 857.14 for 2022.
Aditionnaly, if the "from" field is "After" and the "To" field show a date, it should display an error.
 

vletm

Excel Ninja
kinkart
As I wrote ... that code no need to understand - why it needs to even check?
If 100s of users would modify code themselves or ... even formulas ... wow-effect!
It (code) needs to use with that layout.

case To 202111 from 2023
... backward calculation ... isn't year 2023 after 2021?
Now, it give negative values.
I can modify those full 'years' as full 'years'.
How many 'months' would After have ?

Did You explain Your aditionnaly case before? Why it should display an error? Some of D-column cells has ... text.
As those A-column values are not correct dates, then those can be 'whatever'.
If for some reason eg 202103 is missing from row 2 then that won't notice at all!
Same kind of effect would be, if someone would sort sheet per columns opposite order and so on.
 

kinkart

Member
sorry, I'm not getting it.
let me try to rephrase:
I need to distribute a certain amount over a period of time. For this year and next year, I want to know exactly per month. From 2023 to 2026, per year. After, I don't care whether it's in 2027 or 2059.
If I have 1,000 to distribute between november 2021 and january 2022, then each month I'll have 1000 / 3 (november + december + january).
If I have 1,000 to distribute between 2023 and 2024, then I will have 500 in 2023 + 500 in 2024
If I have 1,000 to distribute between november 2022 and 2023, then I will have 1,000 / (november 2022 + december 2022 + 12 months of 2023) = 1,000 / 14. That will translate into 71.42 in november 2022 + 71.42 in december 2022 + 71.42*12 for 2023.

Is that first clear and more understandable?

Regarding "after", this is an undetermined future, so there is no "month". I can handle this with a formula or with casdaing drop-down to manage this error, ignore my previous comment.
 

vletm

Excel Ninja
kinkart
#1) I tried to ask about this when the "To" field is a month (ie 202111) and the "from" field is a year (ie 2023)
... there from (2023) is after to (202111) ... and my solution gives negative values, because someone has wanted so.
#2) I understand those Your If I have... , those won't be any challenge
#3) After ... if someone has marked After, then always would come show error and no calculation! eg 202111 >> After gives error

... but is it 'error' ... isn't it that someone don't know? Means, missing (disparu[e]) information ... hmm?

There could add more months ... years, the code would take care.
... as well as, there could be 'refresh all'-option.
I can modify those, but is that layout same as You're using?
I would like to know those before I'll do something
... or I could modify as I understood.
Hint: Select E1-cell to refresh all..
 

Attachments

Top