• 513K Members
• 2,625 Online
• 610K Conversations
SOLVED

New Contributor

Quarterly split of revenue

Hi,

I am looking for a formula where I can split my quarterly revenue in to equal amounts so as to get monthly income.

For example:

I am getting a monthly amount of \$300 from Mr. A and a quarterly amount of \$700 from Mr. B in the month of April say on 1st of April.

I have received an amount for a month from Mr. A where as I have received 3 month's amount from Mr. B.

Mr. B's amount will be spread over 3 months (Equally) - April, May & June.

I was looking for a formula to spilt this amount equally in 3 months.

Please note: I might receive the quarterly amount in any month of the year.

Please find attached a pictorial representation of what I see.

Thanks

Regards,

TanmayD

4 Replies
Solution

Re: Quarterly split of revenue

Hi @TanmayD

How about something like the attached file?

Re: Quarterly split of revenue

@TanmayD

Hi,

You have to use two formulas, the start formula is this:

`=\$D2/\$E2`

And you have to insert it in the very first month for each row.

This is the second formula and will depend on the first one:

`=IF(G2=0,0,IF(COLUMN(B1)<=\$E2,\$D2/\$E2,(\$D2/\$E2)-G2))`

Please insert it in the second month and drag it to the right and down.

Please see the attached file.

Regards

Re: Quarterly split of revenue

@Wyn Hopkins  Thank you so much for your help!

Highlighted

Re: Quarterly split of revenue

Thank you so much for your help!

Related Conversations