If function


October 21st, 2012 by nisar nisar

Dear,

Please refer attached sheet & let me know correct formula that will count Tax cell (C10) amount correctly according to Earning cell (B10) amount AS PER THE SLAB MENTIONED.

Thanking you in advance, hope you will favour me by responding.

5 Responses to “If function”

  1. BenneTan BenneTan Says:

    hi there, you should change the values in B5:B7 as such:
    B5: 0
    B6: 5001
    B7: 10001

    then do a VLOOKUP with approximate match. the last argument in VLOOKUP is usually using FALSE, meaning an exact match. this time, do not put anything in the last argument. that will find an approximate match.
    =VLOOKUP(B10,B5:C7,2)

    what it does is to use the value in B10 to search for an exact match in B5:B7. if it doesnt find an exact match, it looks for the next largest value that is LESS than the lookup value & return the value in Column C.

    hope that helps.

  2. nisar r Says:

    Thanks a lot for solution & it really worked. But is there any other way considering only 2 cells. Means suppose if the amount in B10 CELL is below Rs 5000/- then C10 CELL should show Rs 00.00 as tax amount, B10 CELL amount is between Rs 5001 to 10000 then C10 CELL should show Rs 175/- as tax amount & if B10 CELL amount is Rs 10001 & above then C10 CELL should show amount of Rs 200/-. Sorry for putting you in trouble but your solution will help me a lot. Thanks & good night(I am in India)

  3. BenneTan BenneTan Says:

    maybe like this?
    =IF(B10<=5000,0,IF(B10<=10000,175,200))

    or with cell references:
    =IF(B10<=5000,0,IF(B10<=10000,175,200))

  4. nisar r Says:

    absolutely it is this……what I am looking for….THANKS, THANKS, THANKS………Sir, you have saved my lot of time. Hope you will be in touch with me in future too. My email id is nisar5346@gmail.com & I am on fb too, my name is Nisar Rukadikar.

  5. BenneTan BenneTan Says:

    you are very welcome, Nisar
    =)
    you can add us or Like us on our Facebook page:
    https://www.facebook.com/everydayexcel

Leave a Reply

You must be logged in to post a comment.

s2Member®