I have a file attached with raw data. I have client price and a consensus price for each period which is split into months, quarters and years
Problem:
I am trying to have a client price and consensus price for each MONTHLY period however in some cases for example the monthlies in 2029 i do not have a consensus price.
so for example Feb 29 period is Q12029 so steps I want it to do is
1. search for the quarterly consensus price for Q12029 in the rows marked as 'quarterly'
2. If that is blank then that monthly relates to 2029 so take the consensus for annual of that year
3. once it has found a suitable consensus price I want to also grab the desk price on that particular row it found and replace the monthly desk price with this new price
The aim tab in the file is what I want to transform the data into the following format
| | monthly | Quarterly | Annual | method | | Final |
| Austrian Power Base | desk | consensus | desk | consensus | desk | consensus | | | desk | consensus |
| 29/02/2024 | 73.25172414 | 73.24811877 | | | | | monthly | | 73.25172414 | 73.24811877 |
example where consensus is missing on the monthly
| | monthly | Quarterly | Annual | method | | Final |
| Austrian Power Base | desk | consensus | desk | consensus | desk | consensus | | | desk | consensus |
| 28/02/2029 | 64.5 | | | | 64.5 | 66.465762 | Annual | | 64.5 | 66.46576 |
| 31/03/2029 | 64.5 | | | | 64.5 | 66.465762 | Annual | | 64.5 | 66.46576 |
Any guidance on how this could be achieved would be much appreciated. I have already assigned date groupings in the raw tab which could be used to assign group IDs. honestly struggling to figure out how to do this using multi row formulas
