Forum Discussion
Solved
Fee Projection Formula Based on Dates
I'm not sure if a formula I'm describing is possible but I thought I'd ask the group for recommendations. Below is a description: The blue text is intended to be a manual entry, the black te...
- I figured it out mathetes.
Formula is below if you're curious.
Also Retainage is money withheld by one party in a contract to act as security against incomplete or defective works.
=LET(e,EOMONTH(+$N$3:$AP$3,0),(EOMONTH(L4,0)<=e)*IFS(e=EOMONTH(G4,0),D4/K4*E4+D4*F4,e=EOMONTH(I4,0),D4*H4,e<=EOMONTH(L4,J4-1),C4,e<=EOMONTH(L4,J4+K4-1),D4/K4*E4,TRUE,0))
I figured it out mathetes.
Formula is below if you're curious.
Also Retainage is money withheld by one party in a contract to act as security against incomplete or defective works.
=LET(e,EOMONTH(+$N$3:$AP$3,0),(EOMONTH(L4,0)<=e)*IFS(e=EOMONTH(G4,0),D4/K4*E4+D4*F4,e=EOMONTH(I4,0),D4*H4,e<=EOMONTH(L4,J4-1),C4,e<=EOMONTH(L4,J4+K4-1),D4/K4*E4,TRUE,0))
Formula is below if you're curious.
Also Retainage is money withheld by one party in a contract to act as security against incomplete or defective works.
=LET(e,EOMONTH(+$N$3:$AP$3,0),(EOMONTH(L4,0)<=e)*IFS(e=EOMONTH(G4,0),D4/K4*E4+D4*F4,e=EOMONTH(I4,0),D4*H4,e<=EOMONTH(L4,J4-1),C4,e<=EOMONTH(L4,J4+K4-1),D4/K4*E4,TRUE,0))
Good job! I was thinking something along those lines as well. But for your sake--for the sake of learning--you should feel really good to have figured it out on your own. LET lets you do neat things with formulas. To make it even more elegant, you might want to designate other variables for the other EOMONTH calculations, maybe giving them meaningful (abbreviated) names to indicate such things as EPh1, EPh2, ERet1, ERet2.....that way the final formula is actually readable by itself, without needing to refer to the spreadsheet column headings.