gheyman
Well-known Member
- Joined
- Nov 14, 2005
- Messages
- 2,347
- Office Version
- 365
- Platform
- Windows
This formula is calculating a compounded escalation factor.
=IF([@EscalationBaseDate]=[@EscalationEndDate],1,(IFNA((PRODUCT(INDEX(Create_Esc_Mod_Table[#Data],MATCH([@EscalationID],[EscalationID],0),MATCH(IF([@EscalationBaseDate]<2016,2016,([@EscalationBaseDate]+1))&"",Create_Esc_Mod_Table[#Headers],0)):INDEX(Create_Esc_Mod_Table[#Data],MATCH([@EscalationID],[EscalationID],0),MATCH([@EscalationEndDate]&"",Create_Esc_Mod_Table[#Headers],0)))),"")))
I dont want it to look for a match down the [EscalationID] column because in this new table, that I am trying to adopt this formula for, the [EscalationID] may have different yearly rates.
Is there a way to change this so that its just calculating based on the dates and the matching header data?
Thanks for the help!
EscalationID | EscalationBaseDate | EscalationEndDate | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | ||||||||
| 2023 | 2026 |
|
|
|
|
|
|
| ||||||||
| 2024 | 2027 |
|
|
|
|
|
|
| ||||||||
| 2023 | 2025 |
|
|
|
|
|
|
| ||||||||
| 2023 | 2025 |
|
|
|
|
|
|
|
=IF([@EscalationBaseDate]=[@EscalationEndDate],1,(IFNA((PRODUCT(INDEX(Create_Esc_Mod_Table[#Data],MATCH([@EscalationID],[EscalationID],0),MATCH(IF([@EscalationBaseDate]<2016,2016,([@EscalationBaseDate]+1))&"",Create_Esc_Mod_Table[#Headers],0)):INDEX(Create_Esc_Mod_Table[#Data],MATCH([@EscalationID],[EscalationID],0),MATCH([@EscalationEndDate]&"",Create_Esc_Mod_Table[#Headers],0)))),"")))
I dont want it to look for a match down the [EscalationID] column because in this new table, that I am trying to adopt this formula for, the [EscalationID] may have different yearly rates.
Is there a way to change this so that its just calculating based on the dates and the matching header data?
Thanks for the help!