Jun 07 2019 04:35 AM - edited Jun 07 2019 04:37 AM
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
Jun 07 2019 05:18 AM
SolutionJun 07 2019 05:31 AM - edited Jun 07 2019 05:32 AM
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
Jun 07 2019 05:18 AM
Solution