=IF function nesting

[Julian_G57]

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

[#2]

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.

[#3]

@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 

[#4 · Accepted 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

[#5]

@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

[#6]

You’re very much welcome!