Forum Discussion
Fee Projection Formula Based on Dates
- 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))
That's a start. I also said we could use a clear description. At this point, you're in effect asking us to reverse engineer the numbers you show off to the right. Again, you could help us help you if you explain the why behind this schedule; it may seem obvious to you, but even such terminology as "retainage"--which isn't even a word--could be explained; the difference between retainage 1 and retainage 2. While you're at it, all of the dates off on the right are the first of the month, but the dates in the first thirteen columns are all the 15th of their respective months.
On row one, although phase 1 and phase 2 add up to 26, there are in fact 27 months during which payments are shown...why?
AND SO FORTH. I give those just as examples of things that may be entirely clear to you, but aren't intuitive. If we were talking face to face, I could ask questions along the way. Since we're not, you need to explain, making the assumption that you're talking with an intelligent person who is just new to your project and the terminologies, but who is very experienced with Excel. Just explain to that person, in detail and in basic English language (let us do the translation into Excelese) what--given the INPUT of columns A-M--the OUTPUT is to be.
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.
