• 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.

Complex Formula help needed

T

Tareen

New Member
Dear All,


I have 3 WS in a work book for logistic planning. In WS 1, I named a range "City" where I have multiple city names with Data validation list. Then I have second WS where I have 2 differnt columns named "D_T" representing diffrent type of delivery and then I have another column named "WT" representing Waight of shipment and finaly I have diffrent rates for Road Frieght, Air Frieght and Local delivery rates.


I need to formulate a formula with differnt combination of crietaria. Following is the formula but I am unable to trace the error.


=IF(City="Karachi",IF(D_T="Karachi",unit*KC,0),IF(City="Islamabad",IF(D_T="Over Land",WT*OL,WT*ON))),IF(City="Lahore",IF(D_T="Over Land",WT*OL,WT*ON))


OL=Over Land charges

ON=Over Night Charges

Unit=Total Boxes


I have shorten formula else I have 5 cities in total in my City column.


Can anyone suggest a shorter way of doing this, (I am not good in macro or VB) so my only choice is formula base solution.


Thanks in advance.


Tareen
 
Hi, Tareen!


Would you afford using a helper table that links City and D_T combinations? BTW your formula has at least an error whre it says D_T="Karachi" I guess it should say D_T="<sthng.else>.


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Hi Tareen ,


The number of cities can be many ; what is important is how many results will there be ?


From the formula you have posted , I can see :


0 , unit*KC , WT*OL and WT*ON


Are these 4 possibilities the only ones , or are there several more ?


Similarly , for cities , you say you have 5 cities in all ; what are the different possibilities for D_T ?


Can you form a matrix where the cities are listed in 5 rows , and the different possibilities for D_T are listed in multiple columns ? Now fill up the intersecting cells with values which will cover the various possibilities for the results ; if the above 4 possibilities are the only ones possible , you can have values such as 0 , 1 , 2 and 3.


Now , use an INDEX & MATCH combination to retrieve the value ( 0 , 1 , 2 or 3 ) from the matrix , and an IF statement to do the relevant calculation i.e. either 0 , or unit*KC or WT*OL or WT*ON.


Narayan
 
Hi, Tareen!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Complex%20Formula%20help%20needed%20%28for%20Tareen%20at%20chandoo.org%29.xlsx


It's a simple schedule of what you could do to organize your data, and the formula in column F orange responds to the explanation of a theoretical way of calculation.


You should define your own one, arrange your data tables (yellow) as needed, and then apply the formulas (usually search and retrieve values).


Sorry for not been of further help, but without your data and cost structure is very hard to design anything.


Regards!
 
https://www.dropbox.com/s/oysqvr9r2ksr0yf/ISO%20SHEET%20FOR%20CHANDOO.ORG%20-%20Copy.xlsx?m


Dear SirJB7


Above is my complete workbook, highly appreciated if you can help to improve.


Regards,


Sohail Tareen
 
Hi Tareen,


What i have found is that the formula was not executing due to improperly closed braces. Below formula is working for me and giving 9936.27 as a result when placed in 'Imported Goods'!I20:


=IF(City="Karachi",IF(D_T="Karachi",unit*KC,0),IF(City="Islamabad",IF(D_T="Over Land",WT*OL,WT*ON),IF(City="Lahore",IF(D_T="Over Land",WT*OL,WT*ON))))


..But besides that i am doubtful that condition in first IF() when City=Karachi is true is wrong. It should have been either Over Land or Over Night. See bold text.


=IF(City="Karachi",IF(D_T="Karachi",unit*KC,0),IF(City="Islamabad",IF(D_T="Over Land",WT*OL,WT*ON),IF(City="Lahore",IF(D_T="Over Land",WT*OL,WT*ON))))


Regards,
 
Thanks Faseeh,


For Karachi we are using office van so formula is different for Karachi. BTW, can suggest about overall enhancements of this workbook.


Regards,
 
Hi Tareen,


I replaced your existing formula with this one:


Code: 
=INDEX(Sheet1!$L$3:$L$7,MATCH(City&'Imported Goods'!D20,Sheet1!$J$3:$J$7&Sheet1!$K$3:$K$7,0),0)*H20


..using a lookup table in last sheet. please see this:


http://dl.dropbox.com/u/60644346/ISO%20SHEET%20FOR%20CHANDOO.ORG%20-%20Copy.xlsx


Regards,
 
Hi Tareen,


I wrote on the Last Sheet of your workbook in Cells H1:J5 like below:

[pre]
Code: 
Karachi	        Karachi	        30
Islamabad	Over Land	15
Lahore	        Over Land	15
Islamabad	Over Night	25
Lahore	        Over Night	25[/pre]

and Enter following formula in Imported Goods'!I20, Press ctrl+shift+enter:


=INDEX(Sheet1!$J$1:$J$5,MATCH(City&'Imported Goods'!D20,Sheet1!$H$1:$H$5&Sheet1!$I$1:$I$5,0),0)*H20


Hope that helps, i forgot to upload correct file.


Regards,
 
Dear Faseeh,


I was out of town and unable to respond, I made changes as per your instruction but still failed to resolve. Please help, uploaded in my dropbox a file name ISO Version 8, would appreciate if you pleae have a look and make necessory changes for me.


Regards,
 
Hi Tareen,


At the moment Drpbox is giving an error when downloading file, so let me reach home and i will revert. You can access me at faseeh10@hotmail.com as well in case you want to send a sample workbook.


Thanks,

Faseeh
 
You must log in or register to reply here.
Back
Top