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
djclements's avatar
djclements
Bronze Contributor
Feb 27, 2024

lloydantonio Please open the attached workbook and see if the results are what you were aiming for. You can change the Calendar Year from 2021 to 2020 and the results will update accordingly. I'm not entirely sure how you wanted to handle the different currencies, but it's a start anyways...

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
    Bronze 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
        Bronze 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!

    • 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
        Bronze 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