if statement or offset or combination of other functions

[u65646]

I'm having real trouble devising a formula enabling me to have calculate revenue based on delaying the revenue by 1 month and then flat lining the revenue over 12 months


The base data of 2018 orders in cell F6:Q9 to reflect Jan 18-Dec 18
[FONT=Calibri, Helvetica, sans-serif, serif, EmojiFont]The base date of 2019 orders in cells S9:ad9 to reflect Jan 19 - Dec[FONT=Calibri, Helvetica, sans-serif, serif, EmojiFont] 19[/FONT][/FONT]

Currently an order is in Mar 2018 (Cell H6) and the revenue profile would show the 1 month delay there would be no revenue in April but from May 2018, the March order of £20,000 (cell G6) would start to reflect £1,667 from May 18 - Apr 2019.

The formula would need to take account of orders from Jan 2018 til Dec 2019.


The formula would also need to include taking into account any orders from F6:Q9 and S9:AD9 (2018 orders and 2019 orders).
In cell I1, I have entered 1 (to reflect the one month delay)
In cell I2, I have entered 12 (to reflect the number of months the revenue needs to be flat lined by).
The revenue profile cell starts is AE6 and this where I'm having trouble ascertaining what formula to use to capture this.
Please help.

 

[igold]

Cross Posted:

https://superuser.com/questions/132...recognise-revenue-after-1-month-delay/1329921

 

[u65646]

Yes posted the question on superuser too as trying to find solution.

 

[Fluff]

@u65646
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[u65646]

Thanks for letting me know. Will make sure going forward that I will only use this site. The answer to my question has not yet been solved on the other site so would be very grateful if you can assist me in solving the initial question.

 

[Fluff]

Whilst I will not be able to help, it's not clear (to me) what you are trying to do.
I suggest that you post some data (not an image) to the thread showing what your data is like & what you are trying to do.
There are some tools available here to do that & a test board here where you can try them out.