SOLVED
Home

=IF function nesting

%3CLINGO-SUB%20id%3D%22lingo-sub-388392%22%20slang%3D%22en-US%22%3E%3DIF%20function%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388392%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I've%20been%20away%20from%20Excel%20for%20many%20years%20and%20am%20having%20trouble%20trying%20to%20consolidate%20the%20following%20%3DIF%20functions%20into%20one.%20I%20have%20had%20an%20attempt%20at%20it%20but%20it%20keeps%20returning%20%23FALSE.%20Apologies%20if%20this%20has%20been%20covered%20many%20times%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20is%20each%20will%20find%20one%20of%20Monthly%2C%20Quarterly%20or%20Yearly%20in%20a%20column%20to%20the%20left%2C%20then%20return%20the%20calculation%20in%20a%20date%20range%20based%20on%20the%20months%20in%20two%20columns%20with%20start%20and%20end%20dates%20(based%20on%20the%20AND%20calc).%20These%20will%20be%20returned%20in%20a%20July%20to%20June%20monthly%20revenue%20report.%20Hopefully%20this%20is%20enough%20information%20to%20reply.%20If%20not%20please%20feel%20free%20to%20ask.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%24F41%3D%22Monthly%22%2C(IF(AND(J%2438%26gt%3B%3D%24H41%2CJ%2438%26lt%3B%3D%24I41)%2C%24G41%2C0))%2C0)%3C%2FP%3E%3CP%3E%3DIF(%24F41%3D%22Quarterly%22%2C(IF(AND(J%2438%26gt%3B%3D%24H41%2CJ%2438%26lt%3B%3D%24I41)%2C%24G41%2F3%2C0))%2C0)%3C%2FP%3E%3CP%3E%3DIF(%24F41%3D%22Yearly%22%2C(IF(AND(J%2438%26gt%3B%3D%24I41%2CJ%2438%26lt%3B%3D%24I41)%2C%24G41%2C0))%2C0)%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20provide%20a%20small%20sample%20with%20the%20relevant%20functions%20operating.%3C%2FP%3E%3CP%3EThanks%20for%20the%20help.%3C%2FP%3E%3CP%3EJulian%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-388392%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389105%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20function%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389105%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389089%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20function%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20Hi.%20Thank-you%20for%20that.%20I've%20implemented%20it%20and%20it%20works%20perfectly.%20Thank-you%20also%20for%20the%20references%20to%20the%20Revenue%20Recognition%20standards%20-%20as%20is%20the%20case%20in%20many%20instances%20how%20we%20recognise%20income%20for%20the%20internal%20(sales)%20team%2C%20and%20the%20income%20brought%20to%20account%20for%20accounting%2Ftax%20can%20be%203%20different%20things%20unfortunately!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%20and%20thaks%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJulian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388541%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20function%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388541%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Julian%2C%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20J4%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCHOOSE(MATCH(%24F4%2C%7B%22Monthly%22%2C%22Quarterly%22%2C%22Yearly%22%7D)%2C%3CBR%20%2F%3E(J%243%26gt%3B%3D%24H4)*(J%243%26lt%3B%3D%24I4)*%24G4%2C%3CBR%20%2F%3E(J%243%26gt%3B%3D%24H4)*(J%243%26lt%3B%3D%24I4)*%24G4%2F3%2C%3CBR%20%2F%3E(J%243%3D%24I4)*%24G4)%3C%2FP%3E%3CP%3ENote%20the%20following%20references%20for%20revenue%20recognition%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Over%20time%20%3D%20IFRS%2015%2C%20pars.%2035-37%3B%20and%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20At%20a%20point%20in%20time%20%3D%20IFRS%2015%2C%20par.%2038.%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388456%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20function%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388456%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20Hi%2C%20thanks%20for%20responding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20correct%20-%20the%20timing%20of%20services%20is%20a%20little%20complex%20so%20for%20the%20modelling%20I%20have%20made%20the%20assumptions%20you%20have%20identified.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EJulian%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388452%22%20slang%3D%22en-US%22%3ERe%3A%20%3DIF%20function%20nesting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388452%22%20slang%3D%22en-US%22%3EIt%20seems%20that%20for%20those%20with%20monthly%20and%20quarterly%20frequencies%2C%20revenue%20is%20recognized%20over%20time.%20Conversely%2C%20for%20those%20with%20yearly%20frequencies%2C%20revenue%20is%20recognized%20at%20a%20point%20in%20time.%20Please%20clarify.%3C%2FLINGO-BODY%3E
Julian_G57
New Contributor

Hi, I've been away from Excel for many years and am having trouble trying to consolidate the following =IF functions into one. I have had an attempt at it but it keeps returning #FALSE. Apologies if this has been covered many times before.

 

The idea is each will find one of Monthly, Quarterly or Yearly in a column to the left, then return the calculation in a date range based on the months in two columns with start and end dates (based on the AND calc). These will be returned in a July to June monthly revenue report. Hopefully this is enough information to reply. If not please feel free to ask. 

=IF($F41="Monthly",(IF(AND(J$38>=$H41,J$38<=$I41),$G41,0)),0)

=IF($F41="Quarterly",(IF(AND(J$38>=$H41,J$38<=$I41),$G41/3,0)),0)

=IF($F41="Yearly",(IF(AND(J$38>=$I41,J$38<=$I41),$G41,0)),0)

I have tried to provide a small sample with the relevant functions operating.

Thanks for the help.

Julian

 

 

5 Replies
It seems that for those with monthly and quarterly frequencies, revenue is recognized over time. Conversely, for those with yearly frequencies, revenue is recognized at a point in time. Please clarify.

@Twifoo , Hi, thanks for responding.

 

You are correct - the timing of services is a little complex so for the modelling I have made the assumptions you have identified.

 

Regards

Julian 

Solution

Hello Julian, 

In the attached file, the formula in J4, copied down rows and across columns, is: 

=CHOOSE(MATCH($F4,{"Monthly","Quarterly","Yearly"}),
(J$3>=$H4)*(J$3<=$I4)*$G4,
(J$3>=$H4)*(J$3<=$I4)*$G4/3,
(J$3=$I4)*$G4)

Note the following references for revenue recognition: 

1. Over time = IFRS 15, pars. 35-37; and 

2. At a point in time = IFRS 15, par. 38. 

Cheers!

Twifoo

@Twifoo , Hi. Thank-you for that. I've implemented it and it works perfectly. Thank-you also for the references to the Revenue Recognition standards - as is the case in many instances how we recognise income for the internal (sales) team, and the income brought to account for accounting/tax can be 3 different things unfortunately!

 

Regards and thaks again.

 

Julian

You’re very much welcome!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies