ahoward303
New Member
- Joined
- Jun 8, 2018
- Messages
- 7
[MENTION][/MENTION]Good Afternoon Everyone!
I asked a couple of weeks ago for help with a formula that will forecast a value based on a start and end date. In the table below, the following formula is in D2: =IF(AND($A2<=D$1,D$1<=$B2),$C2/DATEDIF(EOMONTH($A2,-1)+1,EOMONTH($B2,0)+1,"m"),""). This formula is absolutely beautiful at adjusting based on the dates entered. The only problem I have now is when we complete one month and move to another. Ideally, when the month of June ends, the $8400 is frozen, meaning, if someone were to go in and change the start and end date, the value of $8400 stays in June. The funds remaining in July - Oct are the only numbers that can be altered. I know in order to freeze June, I would need to copy and paste the value as a number so the formula would be gone. But now, if I do that, the funds in the months of July - Oct change to continue to match the 42000 value it's calculating on. How is the best way to get around that? I've tried reducing the value by a sum of previous months but that doesn't work. Maybe I need to add another column that takes that into account?
<tbody>
</tbody>
I asked a couple of weeks ago for help with a formula that will forecast a value based on a start and end date. In the table below, the following formula is in D2: =IF(AND($A2<=D$1,D$1<=$B2),$C2/DATEDIF(EOMONTH($A2,-1)+1,EOMONTH($B2,0)+1,"m"),""). This formula is absolutely beautiful at adjusting based on the dates entered. The only problem I have now is when we complete one month and move to another. Ideally, when the month of June ends, the $8400 is frozen, meaning, if someone were to go in and change the start and end date, the value of $8400 stays in June. The funds remaining in July - Oct are the only numbers that can be altered. I know in order to freeze June, I would need to copy and paste the value as a number so the formula would be gone. But now, if I do that, the funds in the months of July - Oct change to continue to match the 42000 value it's calculating on. How is the best way to get around that? I've tried reducing the value by a sum of previous months but that doesn't work. Maybe I need to add another column that takes that into account?
Start | End | Value | Jun-18 | Jul-18 | Aug-18 | Sept-18 | Oct-18 |
Jun-18 | Oct-18 | 42000 | 8400 | 8400 | 8400 | 8400 | 8400 |
Jul-18 | Aug-18 | 35000 | 17500 | 17500 | |||
Jul-18 | Oct-18 | 10000 | 2500 | 2500 | 2500 | 2500 | |
Aug-18 | Oct-18 | 20000 | 6666.67 | 6666.67 | 6666.67 |
<tbody>
</tbody>