Results 1 to 10 of 10

Thread: Using IF Function

  1. #1

    Lightbulb Using IF Function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please help me out with the "if" function to calculate the undermentioned arguments in column D of the attached spreadsheet.The idea is to change the tax rate automatically when the basic price is put on column C based on the slabs mentioned below.

    Basic Price Tax Rate
    Upto 4 Lacs 4%
    4 Lacs to 6 Lacs 5%
    6 Lacs to 12 Lacs 6%
    12 lacs to 15 Lacs 6.5%
    15 Lacs to 20 Lacs 7%
    20 Lacs & above 8%
    Attached Files Attached Files

  2. #2
    D2 =IF(C2="","",IF(C2<400000,"4%",IF(C2<=600000,"5%",IF(C2<=1200000,"6%",IF(C2<=1500000,"6.5%",IF(C2<=2000000,"7%",IF(C2>20000000,"8%","8%")))))))

    may be it will work
    Last edited by excuemedubai; 2015-07-04 at 09:06 PM.

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by excuemedubai View Post
    D2 =IF(C2="","",IF(C2<400000,"4%",IF(C2<=600000,"5%",IF(C2<=1200000,"6%",IF(C2<=1500000,"6.5%",IF(C2<=2000000,"7%",IF(C2>20000000,"8%","8%")))))))

    may be it will work
    So are we saying that 1 Lac = 100,000 price units ? If so looks OK.

  4. #4
    Thank you for the reply.The basic rate is the car value excluding VAT and the tax rate is based on the basic value of the car.The tax rate varies according to the slab as mentioned in my query.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by pranjal View Post
    Thank you for the reply.The basic rate is the car value excluding VAT and the tax rate is based on the basic value of the car.The tax rate varies according to the slab as mentioned in my query.
    Thanks. My question was because I didn't understand what was meant by "lac", and therefore I couldn't see where Excuemedubai was getting the numbers from. Now that I've sorted that, I think there is a nought missing in all the numbers except the last one. so (e.g.) the first number should be 4000000, not 400000. Apart from that, the formula looks OK.

  6. #6
    Thanks the formula is working fine as required.

  7. #7
    you are welcome....
    i m feeling happy becoz ur happy with formula.....

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by excuemedubai View Post
    you are welcome....
    i m feeling happy becoz ur happy with formula.....
    So ....... Is 1 lac = 100000 Rupees or 1000000 Rupees (the formula uses both) ???

  9. #9
    Can anyone help me to get the edit the formula for the under mentioned tax slabs :

    Upto 4.00 Lacs 5%
    4.00 Lacs to 6.00 Lacs 6%
    6.00 Lacs to 12.00 Lacs 7%
    12.00 Lacs to 15.00 Lacs 7.5%
    15.00 Lacs to 20 Lacs 9%
    20 Lacs to 30 Lacs 12%
    >30 Lacs 14%

    I have attached the worksheet for reference.I want the formula for coloumn E under Tax Rate
    Attached Files Attached Files

  10. #10
    Quote Originally Posted by pranjal View Post
    Can anyone help me to get the edit the formula for the under mentioned tax slabs :

    Upto 4.00 Lacs 5%
    4.00 Lacs to 6.00 Lacs 6%
    6.00 Lacs to 12.00 Lacs 7%
    12.00 Lacs to 15.00 Lacs 7.5%
    15.00 Lacs to 20 Lacs 9%
    20 Lacs to 30 Lacs 12%
    >30 Lacs 14%

    I have attached the worksheet for reference.I want the formula for coloumn E under Tax Rate
    try this formula..may be it will work..

    =IF(D2="","",IF(D2<400000,"5%",IF(D2<=600000,"6%",IF(D2<=1200000,"7%",IF(D2<=1500000,"7.5%",IF(D2<=2000000,"9%",IF(D2<=3000000,"12%",IF(D2>3000000,"14%","14%"))))))))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •