SOLVED
Home

Quarterly split of revenue

%3CLINGO-SUB%20id%3D%22lingo-sub-677341%22%20slang%3D%22en-US%22%3EQuarterly%20split%20of%20revenue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677341%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20where%20I%20can%20split%20my%20quarterly%20revenue%20in%20to%20equal%20amounts%20so%20as%20to%20get%20monthly%20income.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EI%20am%20getting%20a%20monthly%20amount%20of%20%24300%20from%20Mr.%20A%20and%20a%20quarterly%20amount%20of%20%24700%20from%20Mr.%20B%20in%20the%20month%20of%20April%20say%20on%201st%20of%20April.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20received%20an%20amount%20for%20a%20month%20from%20Mr.%20A%20where%20as%20I%20have%20received%203%20month's%20amount%20from%20Mr.%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMr.%20B's%20amount%20will%20be%20spread%20over%203%20months%20(Equally)%20-%20April%2C%20May%20%26amp%3B%20June.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20looking%20for%20a%20formula%20to%20spilt%20this%20amount%20equally%20in%203%20months.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%3A%20I%20might%20receive%20the%20quarterly%20amount%20in%20any%20month%20of%20the%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20attached%20a%20pictorial%20representation%20of%20what%20I%20see.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ETanmayD%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-677341%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677433%22%20slang%3D%22en-US%22%3ERe%3A%20Quarterly%20split%20of%20revenue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677433%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356456%22%20target%3D%22_blank%22%3E%40TanmayD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%20something%20like%20the%20attached%20file%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117452iC6CECAEC710B7A8D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677438%22%20slang%3D%22en-US%22%3ERe%3A%20Quarterly%20split%20of%20revenue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356456%22%20target%3D%22_blank%22%3E%40TanmayD%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20to%20use%20two%20formulas%2C%20the%20start%20formula%20is%20this%3A%3C%2FP%3E%3CPRE%3E%3D%24D2%2F%24E2%3C%2FPRE%3E%3CP%3EAnd%20you%20have%20to%20insert%20it%20in%20the%20very%20first%20month%20for%20each%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20second%20formula%20and%20will%20depend%20on%20the%20first%20one%3A%3C%2FP%3E%3CPRE%3E%3DIF(G2%3D0%2C0%2CIF(COLUMN(B1)%26lt%3B%3D%24E2%2C%3CBR%20%2F%3E%24D2%2F%24E2%2C%3CBR%20%2F%3E(%24D2%2F%24E2)-G2))%3C%2FPRE%3E%3CP%3EPlease%20insert%20it%20in%20the%20second%20month%20and%20drag%20it%20to%20the%20right%20and%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117453i361A9EE7991A9DE8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-06-07_15-27-51.png%22%20title%3D%222019-06-07_15-27-51.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677702%22%20slang%3D%22en-US%22%3ERe%3A%20Quarterly%20split%20of%20revenue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677710%22%20slang%3D%22en-US%22%3ERe%3A%20Quarterly%20split%20of%20revenue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677710%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
TanmayD
New 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
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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies