• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create formula with multiple conditions from different cells

Uzzal

Member
Hi,
In O21 I have inserted =IF((AND(C21=20,L21<=7)),"6","12") function
I also want that if (C21=40,L21<=7)),"12","24")& (C21=40H,L21<=7)),"18","36")
Please help to do that.
 

Attachments

Chihiro

Excel Ninja
Not exactly sure what your logic is if it matches none of the conditions (assuming it's 36).

in O21: =IFERROR(CHOOSE(MATCH(C23,{20,40,"40H"},0),IF(AND(C23=20,L23<=7),6,12),IF(AND(C23=40,L23<=7),12,24),IF(AND(C23="40H",L23<=7),18,36)),36)

Edit:
If using Excel 2003 or earlier...
=IF(ISERROR(CHOOSE(MATCH(C21,{20,40,"40H"},0),IF(AND(C21=20,L21<=7),6,12),IF(AND(C21=40,L21<=7),12,24),IF(AND(C21="40H",L21<=7),18,36))),36,CHOOSE(MATCH(C21,{20,40,"40H"},0),IF(AND(C21=20,L21<=7),6,12),IF(AND(C21=40,L21<=7),12,24),IF(AND(C21="40H",L21<=7),18,36)))
 

Uzzal

Member
Thanks for the support. Actually I was trying to set a formula to figure out the port storage charges for different type of containers in following manner.
container type days charges
20' 1-7 $6
8-20 $12
for every additional day $24
=========================
40' 1-7 $12
8-20 $24
for every additional day $48
==========================
40H' 1-7 $18
8-20 $36
for every additional day $72
==========================
As I am quite virgin regarding excel formula so I was trying to manage the additional days charge with a separate formula which you will see in the other column of my previously uploaded file. Hope I could delivered my message.
 

Somendra Misra

Excel Ninja
Hi,

I would suggest you not to hard code the container type or the rates and days directly in the formula, as it will be really hard if you want to change any of these parameters in future. You need to do it in all the cells. I would suggest you to make a rate table as I had done in your sample file and than use a formula to get the rate.

See the attached file and comment back.

Regards,
 

Attachments

Uzzal

Member
Yaaaar ! this is brilliant.
Somendra, Kasey aso, tomakey ekta chumu dei, ummm....
I got the some thing to play with for the following couple of hours, I want to make some notice further, for 22 days demurrage it should be $12 for the first 20days plus $24X2 additional days total $60 dollars.
 

Somendra Misra

Excel Ninja
Yaaaar ! this is brilliant.
Somendra, Kasey aso, tomakey ekta chumu dei, ummm....
I got the some thing to play with for the following couple of hours, I want to make some notice further, for 22 days demurrage it should be $12 for the first 20days plus $24X2 additional days total $60 dollars.
Hi,

See this file.

Regards,
 

Attachments

Uzzal

Member
Sorry, sorry , sorry............I want to make some notice further, for 22 days demurrage it should be $12x20 days=$240 for the first 20days plus $24X2=$48 additional days total $288 dollars.
 

Uzzal

Member
Sorry, Yaar:confused: I was a little bit late to response. I didn't expect that you will rework so quickly.I feel ashamed of my clamminess.
 

Somendra Misra

Excel Ninja
Sorry, sorry , sorry............I want to make some notice further, for 22 days demurrage it should be $12x20 days=$240 for the first 20days plus $24X2=$48 additional days total $288 dollars.
What will be the charges for less than 20 days, say 4 days and 18 days?

Regards,
 

Uzzal

Member
First of all there are two type of charges from different authorities Shipping line & port
From shipping line for first 5 days its free.
then it start charging in different spreads for different type of containers

for example: against a 20' container for extra 4 days(after 5 free days) they will charge 4*$6=$24, but when it will become extra 18 days(after 5 free days), they will charge
(7days*$6=$42)+(7days*$9=$63)+(4days*12=$48)=Total $153
finally when it will exceed additional 21days(after 5 free days) $18 will be added for every more day.this sequence will change when the size of the container will change.
upload_2015-10-26_13-8-32.png

But regarding port authority
They allow 4 days free, after that the charge started in following manner
upload_2015-10-26_13-25-21.png



Against a 20' container for the first 4days (after 4 free days) they will charge 4days*$6=$24
& for additional 18 days (after 4 free days) it will stand for
7days*$6=$42
11days*$12=$132
===============
Total $174
and when the additional days will exceed 20days range(after 4 free days) they will charge $24 for every subsequent day like as follows
against 25 extra days(after 4 free days)
7days*$6=$42
13days*$12=$156
5days*$24=$120
================
Total $318
again this sequence will change when the size of the container will change.

Thanks for your long concentration.
 

LofungMick

New Member
Hello,

The original question posed by Uzzal and the solution/formula created by Somendra Misra is great and I learnt a lot. But I would like to extend it a bit so I hope somebody on this blog can help out.


The attached Workbook “Detention Calculator – Chandoo.xlsx” is based on the original from the thread. Its basic structure and the formula is the same. But the solution has two limitations:

· The first is that it addresses one Shipping Line only.

· The second is that the Shipping Line has the same Date Ranges/Tiers across all Types of Containers in its inventory.


But in reality there are many Shipping Lines, each one will deal in different types of containers, each will have different Date Ranges/Tiers as against each other and each one will have different daily charge rates within each Date Range/Tier. Finally within the same Shipping Line there can be different Date Ranges/Tiers for different containers.


The structure and the formula, as presented, cannot handle all these variables. Therefore each Shipping Line has to have its own Worksheet allowing the structure and formula to deal with only one set of variables at a time. Which is cumbersome.


The Shipping Lines created in the attached Workbook are Uzzal, Acme, Greenseas, PSA and Sofran and are representative of the types of variables that can be encountered. They are all essentially the same in structure with Acme having a different way of treating particular containers.


So the main question is: Is it possible to create a solution so that all the variables can be calculated on a single Worksheet instead of one per Shipping Line?

· At the moment with one sheet for each Shipping Line, we enter the Type of Container And the Number of Days Detention and the formula returns the Total Cost of Detention owed to the Shipping Line represented by that Worksheet. You could end up with 20 x Worksheets depending on how many Shipping Lines you did business with.

· Instead it would be better if there was only one Worksheet for all Shipping Lines. Then you would enter the Shipping Line And the Type of Container And the Number of Days Detention. The result is the Total Cost of Detention for the selected Type of Container owned by the selected Shipping Line.

· I tried this by creating a single All Ships Lookup Table with all Shipping Lines and their data in five columns. See the last Worksheet in the attached Workbook. Then I attempted to create an INDEX-MATCH formula but got nowhere.


A Second Question. If we look at the Worksheet calculator for the “Acme Shipping Line” we can see there are in fact two calculators – to cater for the two different Tier systems (one for containers that have only one Tier i.e. daily rate is charged from Day 1 and the second for those with two Tiers).

Question: Is there any way to merge the two into a single formula?


A Third Question. In the attached examples the data is presented in Landscape orientation with the Type of Container running across the top and the Date Ranges/Tiers running down. But say we want to change the orientation to Portrait with the Type of Container on the left in the first column running down the page and the Date Ranges/Tiers running across the top.

Problem: See the example in the last Worksheet called “Re-Orientated”. Obviously the formula has to be amended which I have attempted. But there is a problem as can be seen in the example. Perhaps a kind person can point out where I went wrong with the amended formula.

Thank you.
 

Attachments

vletm

Excel Ninja
ayun09
Please, reread Forum Rules and
after that, You'll notice that You should open a new thread.
With that new thread,
You would remember those hints from Forum Rules - How to get the Best Results at Chandoo.org.
 
Top