Revenue spread over months

**dupperco** · 04-21-2009, 09:07 PM

Hello. I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.

I have attached a snapshot of what I am trying to do.

I really appreciate any help.

Thanks.

**dupperco** · 04-21-2009, 11:21 PM

Hello. I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.

I have attached a snapshot of what I am trying to do.

I really appreciate any help.

Thanks.

**Justin Young** · 04-22-2009, 12:33 AM

Dupperco,

I had a look at your attached example and I am not sure that I can follow how you derived those percentages for M1,M2 etc. Anyhow, I have attached a solution to what I think you want to do.

Let me know if you want any further assistance.

Regards,
Justin

**dupperco** · 04-22-2009, 02:16 AM

Justin,
Thanks. I will have to study it a bit.

I have made some progress, but have another question. I have these two VLOOKUP strings in one equation and they are not equaling what they should be. If I separate the two they are fine but I might have a syntax issue. Any help would be appreciated.

=VLOOKUP(Variables!$B2,'Response Curve'!$A$12:$R$192,2,FALSE)*(Variables!$K2*Variables!$F2*(Variables!$F$29+Variables!$H$31)+VLOOKUP(Variables!$B2,'Response Curve'!$A$12:$R$192,2,FALSE)*(Variables!$K2*(Variables!$G2+Variables!$I2)))

**rylo** · 04-22-2009, 02:17 AM

Hi

What do you want to happen with the output? All put into one row, or a new row for each revenue item?

As you have come to the programming forum, I'm assuming that you want a macro to do the processing of the list of data in column C.

rylo

**royUK** · 04-22-2009, 02:59 AM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to Functions Forum

**DonkeyOte** · 04-22-2009, 03:14 AM

There are a few approaches... I've put two into the attached, both utilising Sumproduct... the matrices helper approach will I suspect may make your life easier in the long run...

I wasn't sure as to the significance of 8th of Month - in the example the months were revised to 1st of.

I have attached in .xls format should this prove useful to others going forward.

**DonkeyOte** · 04-22-2009, 03:21 AM

[removed - mods since merged threads]

**DonkeyOte** · 04-22-2009, 03:22 AM

[removed - mods since merged threads]

**royUK** · 04-22-2009, 03:34 AM

dupperco, I stronglyrecommend that you actually read the Forum Rules that you agreed to when joining. You have posted in the wrong forum & also duplicated the post in another forum. This makes more work for everyone and could be avoided by following the Rules. next time your posts will be closed.