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
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
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies