Forum Discussion
=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
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 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
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
