Forum Discussion

lloydantonio's avatar
lloydantonio
Copper Contributor
Feb 27, 2024

Quarterly revenue formula help needed

Hi all, I need some urgent help today if anyone has any ideas. I have revenue with start date and end date (e.g. 10th January to 5th December) and need a formula to allocate the proportional revenu...
excel
Formulas and Functions
lloydantonio's avatar
lloydantonio
Copper Contributor
Feb 27, 2024
Thats amazing thankyou. Quick questions:
(1) How does the info in row2 work? The EOMONTH(2)?
(2) is it possible to do all of this ONLY using the green cells. In other words to not have the orange text you added to the top and in some way incorporate it into the formula itself?
djclements's avatar
djclements
Silver Contributor
Feb 27, 2024

lloydantonio No worries, in the newly attached workbook (v2) I used the DATE function to reference the quarterly start and end dates directly within the IF formula, and simplified the logic used in my previous response. I also removed the "Days" column and calculated it directly within the same formula.

 

Regarding the EOMONTH formula, it can be used to return the last day of the month, n months in the past or future. If you're interested in learning more about this function, please see: https://exceljet.net/functions/eomonth-function 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 27, 2024

    djclements 

    Surely this is not your usual style! :suprised:

    This is taken directly from the djclements formula but refactored to give the result as a dynamic array.

    =LET(
    qtr, {1, 2, 3, 4},
    closeDate, IF(
        DATE(calendarYear, 1 + 3 * qtr, 0) < End,
        DATE(calendarYear, 1 + 3 * qtr, 0),
        End
    ),
    openDate, IF(
        DATE(calendarYear, 1 + 3 * (qtr - 1), 1) > Start,
        DATE(calendarYear, 1 + 3 * (qtr - 1), 1),
        Start
    ),
    partQtr, (closeDate - openDate + 1) / (End - Start + 1),
    percent, MAP(partQtr, LAMBDA(x, MAX(x, 0))),
    revenue, percent * totalRevenue,
    HSTACK(percent, revenue)
)

    There are slight differences in the results, but I think that is due to the difference in calculating month ends (otherwise it may be me fouling up).

    • djclements's avatar
      djclements
      Silver Contributor
      Feb 27, 2024

      PeterBartholomew1 Nice adaptation 🙂 I didn't notice any differences in the results (=MyRange=YourArray returned all TRUE's), so it looks like everything checks out.

       

      I'm not too sure I have a "usual" style, lol. It can vary from one scenario to the next. I do so enjoy the challenge of writing complex, single-cell dynamic array formulas, but also appreciate the simplicity of old-school formulas, which when applied to an Excel table are dynamic enough in their own right. Going full-blown dynamic array right off the bat can sometimes fly right over the heads of your intended audience. 😉

       

      Cheers!

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Feb 27, 2024

        djclements 

        I guess lloydantonio gets the deciding vote on which version is preferred.

         

        I tend to write the solutions I like, if nothing else to demonstrate that the approach based upon defined names and dynamic arrays is widely applicable.  With you on the thread, I was sure of an audience of one and I can always apologise to the OP if it comes to that :happyface:.

  • lloydantonio's avatar
    lloydantonio
    Copper Contributor
    Feb 27, 2024
    Wow thats great!

    What are the +1 for at the end of the formula?
    • djclements's avatar
      djclements
      Silver Contributor
      Feb 27, 2024

      lloydantonio The +1 is used to return the correct number of days between two dates. For example, there are 31 days in January, but when you subtract January 1, 2021 from January 31, 2021, it returns 30. The +1 is needed to return 31.

Resources