I am trying to calculate multiple scenarios from my cells and I know there are several IF, WHERE, THEN, etc. clauses I probably need to use, but this is too advanced for me. I feel like I'm making this way more complicated than it needs to be. I could update the table applying simple math, but since I may not be the person entering the data down the road, I would like to make this automated for anyone to use.
Scenario:
- FY21 funds rolled over to FY22
- FY22 charges subtract from FY21 funds first until 0, then begin subtracting from FY22 funds
- Subtract additional charges from FY22 funds (including any leftover charges after FY21 hits 0)
- Up to $1000 of remaining FY22 funds rollover to FY23
- NO FY21 funds can roll over to FY23
I attached my spreadsheet with what I'm looking to accomplish. The table can be set up any way it needs to be in order to support the formulas, so I'm open to suggestions.
