Mar 25 2019 04:29 PM
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
Mar 25 2019 10:13 PM
Mar 25 2019 10:19 PM
@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
Mar 26 2019 02:53 AM
SolutionHello 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
Mar 26 2019 02:58 PM
@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
Mar 26 2019 02:53 AM
SolutionHello 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