I am trying to build a formula where the percentage of inflows will only calculate based on YTD totals up to a cap. For example, my inflows and percentages are constant, but when the YTD reaches 25,000 I want the formula to calculate the percentage on the delta. In other words, row 4 the 2% is based on 5,000 (remaining amount before reaching the cap) for a result of 100. Subsequent inflows and percentage calculations are turned off due to passing the cap.
For context, I am trying to build the formula where I can enter in different cash flow amounts, but the YTD cap (25,000) remains the same. So if the inflows were 20,000 it will automatically adjust. I hope I explained enough for others to help. Let me know if you need clarification. Thanks!
For context, I am trying to build the formula where I can enter in different cash flow amounts, but the YTD cap (25,000) remains the same. So if the inflows were 20,000 it will automatically adjust. I hope I explained enough for others to help. Let me know if you need clarification. Thanks!
| Inflows | Percentage - 2% | YTD |
| 10,000 | 200 | 10,000 |
| 10,000 | 200 | 20,000 |
| 10,000 | 100 | 30,000 |
| 10,000 | 0 | 40,000 |
| 10,000 | 0 | 50,000 |
