Spilled formula calculating revenue by quater or year

Hi, I am trying to create a formula takes all project from a table and spills those projects in revenues per quarter or year (both would be handy to have) and chops the revenues in those quarters o...

RevenueByQuarter.xlsx10 KB

community

excel

Formulas and Functions

djclements

May 23, 2025

Perhaps something along these lines:

=LET(
   i, IF(tbl_Projecten[Duration],tbl_Projecten[Duration],1),
   j, SEQUENCE(,MAX(i),0),
   t, i>j,
   p, TOCOL(IFS(t,tbl_Projecten[Project]),2),
   v, TOCOL(IFS(t,tbl_Projecten[Revenues]/i),2),
   d, TOCOL(IFS(t,EDATE(+tbl_Projecten[Startdate],j)),2),
   q, QUOTIENT(MONTH(d)-1,3)+1,
   y, YEAR(d),
   a, PIVOTBY(HSTACK(y,q),p,v,SUM,0,2,,1),
   VSTACK(HSTACK({"Year","Quarter"},DROP(TAKE(a,1),,2)),DROP(a,1))
)

See also the Conditional Formatting rules in the attached file...

djclements

Bronze Contributor

May 23, 2025

Perhaps something along these lines:

=LET(
   i, IF(tbl_Projecten[Duration],tbl_Projecten[Duration],1),
   j, SEQUENCE(,MAX(i),0),
   t, i>j,
   p, TOCOL(IFS(t,tbl_Projecten[Project]),2),
   v, TOCOL(IFS(t,tbl_Projecten[Revenues]/i),2),
   d, TOCOL(IFS(t,EDATE(+tbl_Projecten[Startdate],j)),2),
   q, QUOTIENT(MONTH(d)-1,3)+1,
   y, YEAR(d),
   a, PIVOTBY(HSTACK(y,q),p,v,SUM,0,2,,1),
   VSTACK(HSTACK({"Year","Quarter"},DROP(TAKE(a,1),,2)),DROP(a,1))
)

See also the Conditional Formatting rules in the attached file...

MichielS340
Brass Contributor
May 23, 2025
Many thanks, I think I can use this as a basis! 🙏

Forum Discussion

Spilled formula calculating revenue by quater or year

Resources