Forum Discussion
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
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
Thank you so much for your help!
Wyn Hopkins Thank you so much for your help!
