# Quarterly revenue formula help needed

Copper Contributor

# 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 revenue to each quarter of that year.

One complication is that the start dates include some data from 2020 however I only want to see the revenue in 2021 Q1-Q4

Any help would be greatly appreciated!

8 Replies

# Re: Quarterly revenue formula help needed

@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...

# Re: Quarterly revenue formula help needed

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?

# Re: Quarterly revenue formula help needed

@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

# Re: Quarterly revenue formula help needed

Wow thats great!

What are the +1 for at the end of the formula?

# Re: Quarterly revenue formula help needed

@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.

# Re: Quarterly revenue formula help needed

Surely this is not your usual style!

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).

# Re: Quarterly revenue formula help needed

@Peter Bartholomew 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!

# Re: Quarterly revenue formula help needed

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 .