LofungMick
New Member
Hello All,
This post may appear to be the same as one I did a few days ago on an old thread (“Create Formula with Multiple Conditions From Different Cells”) but I believe it is different. Both are trying to solve the same problem based on a tiered rate structure. The earlier one does it by calculating the cumulative amount based on the different rates at each tier and then adds up each Tier. This one will attempt to do it all in a single cell with a single formula.
I also posted same question at Mr Excel back in September last year but no responses https://www.mrexcel.com/forum/excel...culating-rental-payable-multiple-factors.html
The Specific Problem Here Is: In a single formula, calculate the amount of detention payable on a particular type of shipping container leased from a Shipping Line given the number of billable days and the daily rate. This daily rate can either be a flat rate or tiered depending on the type of container and Shipping Line. Presumably I can migrate the solution to similar projects such as a tiered sales commission or progressive taxes.
Two Workbooks are attached. One shows the Detention Rates for some Shipping Lines which shows the large number of variables we have to deal with. The second contains the actual workings. Columns P and R are the two cells where the formula/calculation needs to occur.
There are two calculations which require a more sophisticated solutions than an IF Function which has severe limitations anyway.
The First has to identify the number of “Import Detention Free Days” (Column J) applicable to the unique combination of Shipping Line and Type of Container specified in Columns H and I. This part I can do using a look up table and an Index-Match with an array formula as follows (Column J again):
{=INDEX('FREE DAYS LOOKUP WEF 01 JUN 16'!$A$1:$C$90,MATCH(1,('FREE DAYS LOOKUP 01 JUN 16'!$A$1:$A$90=I2)*('FREE DAYS LOOKUP 01 JUN 16'!$B$1:$B$90=H2),0),3)}
Once the container is returned you can then calculate the number of ‘billable days’ using simple arithmetic and at this point you multiply that number by the daily rate. The result is what we owe to the Shipping Line for that container and is the end of the process.
It is this final calculation I need help with. Have tried another Lookup Table showing all the rates but the necessary INDEX MATCH plus whatever else beats me. See the last Worksheet called “All Ships Rates Lookup” – this is all the Shipping Lines and their rates in one Lookup table.
A final question. If a Shipping Line changes any of the variables (types of containers, free days, tiers, daily rates), how would that be handled in the design? This is important because any such changes must apply only from the effective date going forward leaving all historical data unaffected by any changes. In the “free days” lookup/INDEX-MATCH solution a new lookup table with a different name and amended data works fine.
Thank you and grateful for any responses.
This post may appear to be the same as one I did a few days ago on an old thread (“Create Formula with Multiple Conditions From Different Cells”) but I believe it is different. Both are trying to solve the same problem based on a tiered rate structure. The earlier one does it by calculating the cumulative amount based on the different rates at each tier and then adds up each Tier. This one will attempt to do it all in a single cell with a single formula.
I also posted same question at Mr Excel back in September last year but no responses https://www.mrexcel.com/forum/excel...culating-rental-payable-multiple-factors.html
The Specific Problem Here Is: In a single formula, calculate the amount of detention payable on a particular type of shipping container leased from a Shipping Line given the number of billable days and the daily rate. This daily rate can either be a flat rate or tiered depending on the type of container and Shipping Line. Presumably I can migrate the solution to similar projects such as a tiered sales commission or progressive taxes.
Two Workbooks are attached. One shows the Detention Rates for some Shipping Lines which shows the large number of variables we have to deal with. The second contains the actual workings. Columns P and R are the two cells where the formula/calculation needs to occur.
There are two calculations which require a more sophisticated solutions than an IF Function which has severe limitations anyway.
The First has to identify the number of “Import Detention Free Days” (Column J) applicable to the unique combination of Shipping Line and Type of Container specified in Columns H and I. This part I can do using a look up table and an Index-Match with an array formula as follows (Column J again):
{=INDEX('FREE DAYS LOOKUP WEF 01 JUN 16'!$A$1:$C$90,MATCH(1,('FREE DAYS LOOKUP 01 JUN 16'!$A$1:$A$90=I2)*('FREE DAYS LOOKUP 01 JUN 16'!$B$1:$B$90=H2),0),3)}
Once the container is returned you can then calculate the number of ‘billable days’ using simple arithmetic and at this point you multiply that number by the daily rate. The result is what we owe to the Shipping Line for that container and is the end of the process.
It is this final calculation I need help with. Have tried another Lookup Table showing all the rates but the necessary INDEX MATCH plus whatever else beats me. See the last Worksheet called “All Ships Rates Lookup” – this is all the Shipping Lines and their rates in one Lookup table.
A final question. If a Shipping Line changes any of the variables (types of containers, free days, tiers, daily rates), how would that be handled in the design? This is important because any such changes must apply only from the effective date going forward leaving all historical data unaffected by any changes. In the “free days” lookup/INDEX-MATCH solution a new lookup table with a different name and amended data works fine.
Thank you and grateful for any responses.