Return value in IFS based on date

[Jpalaci1]

I want to show the spread of monthly spend on a timeline from January 2021 to December 2022. I used 1-24 to put the right value in the right month. I'm having trouble with the following:

1) I use =IFNA(IFS($B$5<=E3,"Stage 1",$C$5<=E3,"Stage 2",$D$5<=E3,"Stage 3"),"-") to place the costs in the correct cell/month. It works but I don't know how to start and end them; that is, on row 16 is how I want the result to be. Start on the month it should and end after it exceeds the value.

I also run into the issue where say stages may overlap/occur on top of each other. How would I show the one that occurs over the other?

2) Is there an easier way to drag down the formula? I can drag across but then for each column I must unlock or change the absolute reference. I need to do this over multiple years so it would be ideal to be able to modify easier to drag across then down or vice versa.

3) Is this the best approach to this?

4) Could this be easier achieved in another way in Excel or in Power BI? This is sort of a spend gantt/visual who to see the monthly spread over a period of time is the goal. 

[#2 · Accepted solution]

@Jpalaci1 See attached. I didn't change your approach. Just amended the formula for you. Be aware that you need to use the $ signs in the correct places if you want to be able to drag the formulae across. Note also that you need to test for months from stage 3 down to stage 1. See attached.

[#3]

Thank you for this. If there was an alternative to this, what would it be in terms of handling the output in a better manner/different approach?

[#4]

@Jpalaci1 You could work with real dates instead of month numbers. That allows you to use some of Excel's built-in date intelligence functions. Added a small example in the attached file. And then there are probably dozens of other thinkable solutions.