SOLVED

# =IF function nesting

Highlighted
Occasional Contributor

# =IF function nesting

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

# Re: =IF function nesting

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.
Highlighted

# Re: =IF function nesting

@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

Highlighted
Solution

# Re: =IF function nesting

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

Highlighted

# Re: =IF function nesting

@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

Highlighted

# Re: =IF function nesting

You’re very much welcome!