SOLVED

Quarterly split of revenue

Copper Contributor

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
best response confirmed by TanmayD (Copper Contributor)
Solution

Hi @TanmayD 

 

How about something like the attached file?

 

image.png

 

 

@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.

 

2019-06-07_15-27-51.png

 

Please see the attached file.

 

Regards

@Wyn Hopkins  Thank you so much for your help!

Thank you so much for your help!

1 best response

Accepted Solutions
best response confirmed by TanmayD (Copper Contributor)
Solution

Hi @TanmayD 

 

How about something like the attached file?

 

image.png

 

 

View solution in original post