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

Help Required - Designing Logic Using Minimum "IF" statements

Hi All !

i am working on a requirement put forward by one of my friend who has to do manual entries in excel for booking the invoices . Its basically a courier company which has billing pattern as follows.

There are several filter categories according to which billing is done . These categories can be classified as follows.

1. First he needs to See whether Parcel is for Local delivery( Within state) or Out of state delivery.
2. If its local , then if it is a "document" type or "Non-document ( heavy parcels)"
3. Now consider my docket is "local" and is a "document"
4. Now there are weight slabs starting from 0 to 5Kgs , with each slab of 250gms
5. Billing is done as per the following three main parameters - Local / outbound , Doc/non-doc , Weight slab

I am attaching a file herewith where i have written some basic login using "IF" ( too lengthy though) . Please help me to overcome this problem.

it will be really great if you can send logic in Excel as i am not familier with Macros.

Await your reply.

Thanks ,

Aadi
 

Attachments

  • Sample.xlsx
    13.9 KB · Views: 4
Would using a lookup table constructed as follows help
upload_2014-10-18_13-13-2.png
Where Base Weight is the 0-5 Kg, Incremental weight is the 250g

See attached file

I believe that from the above table we can calculate everything

If you can populate that I think we can assist you
 

Attachments

  • Sample-Hui.xlsx
    12.2 KB · Views: 3
Hi,

See the file, I had included two more columns, a named formula and adjust your rate charts. Check for the result by various combinations.

Regards,
 

Attachments

  • Sample (42).xlsx
    12.1 KB · Views: 3
Hi Somendra sir ,

I have gone through your file , can you please help me explain the exact logic.
what i have understood is you have made 4 categories viz DL , DN , OD , ON and accordingly linked the rates .
as well as can you please help to clear some doubts in the attached file.

thanks in advance for your support.

- Aadi
 

Attachments

  • Sample_(42)(1).xlsx
    12.3 KB · Views: 4
Hi Aaditya,

1. What you see in B4:B20 & C4:C20 is not filter, it is called data validation. Refer below link for the same
http://office.microsoft.com/en-001/excel-help/apply-data-validation-to-cells-HP010072600.aspx#BM4_1
Go to the section of drop-down list.

2. RateChart is the named formula to select rate table based on Docket Type & Location. You can see the formula in Name Manager. To access Name Manager You can go to name manager through Formula tab or by keyboard shortcut Ctrl+F3.
I had used CHOOSE function in there.

3. The combination table that I created is used in the above formula.

Go through them and write back.
 
Back
Top