Quarterly revenue formula help needed

Question

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 revenue to each quarter of that year.&nbsp;One complication is that the start dates include some data from 2020 however I only want to see the revenue in 2021 Q1-Q4&nbsp;Any help would be greatly appreciated!&nbsp;

peterbartholomew1 · Answer

djclements&nbsp;Surely this is not your usual style!&nbsp;This is taken directly from the&nbsp;djclements&nbsp;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) &lt; End,
        DATE(calendarYear, 1 + 3 * qtr, 0),
        End
    ),
    openDate, IF(
        DATE(calendarYear, 1 + 3 * (qtr - 1), 1) &gt; 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 · Answer

lloydantonio&nbsp;Please open the attached workbook and see if the results are what you were aiming for.&nbsp;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 · Answer

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 · Answer

lloydantonio&nbsp;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.&nbsp;Regarding the EOMONTH formula, it can be used to&nbsp;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:&nbsp;https://exceljet.net/functions/eomonth-function&nbsp;

lloydantonio · Answer

Wow thats great!What are the +1 for at the end of the formula?

Forum Discussion

Quarterly revenue formula help needed

8 Replies

Resources