Help with Excel Array/Formula

[adamkpl]

Greetings all and happy new year - sorry for the solicitation but desperate to get this working asap to avoid manual work !!!

I'm trying to create a formula to accomplish the following. I am looking for a completed excel template I can use and post the formula/excel file here for others' use.

I have 4 columns in an excel worksheet...
(1) Start Date - when the revenue stream begins (date field)
(2) Revenue Amount - dollar currency field
(3) Arrangement - Either "One off" or "Recurring"
(4) # of Months - If one off, how many months of revenue (number 1-12)

For each row I need a formula which will generate an array of monthly revenue based on this logic. I prefer a formula over a macro.

If it's a one off, the formula will simply divide the monthly revenue by the # of months and begin on the start date.
If its a recurring row, the formula will divide the revenue by the # of months and begin on the start date but follow a ramp pattern (month #1 is 33%, month #2 is 50%, month #3 is 66, month 4 is 75%, then 100% in month 5)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Revenue[/TD]
[TD]Arrangement[/TD]
[TD]# Months[/TD]
[TD]Jan, '18[/TD]
[TD]Feb, '18[/TD]
[TD]....[/TD]
[TD]Dec, '18[/TD]
[TD]Jan, '19[/TD]
[TD]Feb, '19[/TD]
[TD]...[/TD]
[TD]Dec, '19[/TD]
[/TR]
[TR]
[TD]Jan 1, 2018[/TD]
[TD]$120[/TD]
[TD]One-Off[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]60[/TD]
[TD]...[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Jan 15, 2018[/TD]
[TD]$120[/TD]
[TD]One-Off[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]...[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]...[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Feb 1, 2018[/TD]
[TD]$60[/TD]
[TD]Recurring[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1.65[/TD]
[TD]...[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]...[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance!!!

 

[Gerald Higgins]

Re: Help with Excel Array/Formula - Will pay

Hi, this looks like it might contravene rule #5 . . .

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

It looks like what you want might be easily achievable with relatively simple formulas.
Can I just check, is the ramp pattern ALWAYS 33%, 50%, 66%, 75% and 100%
Also, are the Start Dates always the first of the month ?
I assume we don't need to worry about different month lengths ?

 

[adamkpl]

Re: Help with Excel Array/Formula - Will pay

Hi, this looks like it might contravene rule #5 . . .

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

It looks like what you want might be easily achievable with relatively simple formulas.
Can I just check, is the ramp pattern ALWAYS 33%, 50%, 66%, 75% and 100%
Also, are the Start Dates always the first of the month ?
I assume we don't need to worry about different month lengths ?

Ooops sorry I didnt realize it would be against the TOS - sorry in that case ill redit my original post. Anyways, the ramp is always the same assuming its recurring (if one off then no ramp). The start date doesnt matter so long as the revenue stream is in that month so if Jan 28 the full revenue gets counted in Jan.

Thanks in advance!

 

[Gerald Higgins]

Re: Help with Excel Array/Formula - Will pay

Sorry, I also need to check, how is the date stored in the header row, for example "Jan 18" ?

One way, not the only way but it might make things simpler elsewhere, would be to do it as a full Excel date, 01/01/2018, and apply a "MMM YY" format so that it appears as "Jan 18" and so on.