Forum Discussion
Calculate a Fee if between a Date and on ProjID
=INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Nice. Very nice.
Could I possibly add a tweak to it?
If the date is outside of the rates table, meaning that the date in the raw data is >[Date To] then fall back to the rate that is the according to the next closest date? Right now, it will return a #N/A if the date is > the [Date To].
thank you very much.
=IFERROR(INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360),INDEX(Table2[Rate],MAX(IF(Table2[ProjID]=B2,ROW(Table2[ProjID]))-14))*[@Amt]*(30/360))Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Wow. I can sort of understand it.
I do have a question though when you have a sec.
The example posted was a simple example.
I have a different file in that the rates table is on another sheet.
I think my issue is the -14 part.How would I adjust knowing that the rate table is on another sheet?
Does -14 correspond to the header row for the rate table? ,
Do I need to reference the sheet that the rate table is on.
Also, I have 1 additional column in the rate table to the left of the rate (I dont think its material though).
The pic below is my production file (its a little different from the attached xl).
You can see my rate table is on another sheet and my data is on the sheet named "data".
==>I assume I would put -3 where the -14 is?
I tried this but it didnt seem to work (still says #NA if outside the dates max but in your file posted it works perfectly)
=IFERROR(INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360),INDEX(Table2[Rate],MAX(IF(Table2[ProjID]=B9,ROW(Table2[ProjID]))-14))*[@Amt]*(30/360))=IFERROR(INDEX(Table3[Rate],MATCH(1,([@ProjID]=Table3[ProjectID])*([@Date]>=Table3[Date from])*([@Date]<=Table3[Date to]),0))*[@Amt]*(30/360),INDEX(Table3[Rate],MAX(IF(Table3[ProjectID]=B2,ROW(Table3[ProjectID]))-3))*[@Amt]*(30/360))Maybe with this formula. I added some rows in your rate table on the sheet "Rates" in order to check if the formula works as intended.
The name of the sheet "Rates" doesn't appear in above formula. Only the name of the table which is Table3 is referenced in the formula.
The -14 corresponds to the header row of the rate table. In other words: The data of the rate table is in rows 15 to 19 of the worksheet. These rows are rows 1 to 5 within the rate table (Table2). Consequently i had to enter -3 in the above formula for Table3.