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

how to solve too many IF situation?

fred

Member
Hi all,


I have a questions on picking out the right price for the quantity ordered. I doubt the function "if" can handle that many tiers of separation. How would you suggest me to fix this? How would "Choose" fix this?


Quantity Price/Unit

1-50 $100

51-110 $98

111-240 $95

241-295 $91

296-375 $86

376-550 $80

551-625 $73

626-745 $65

746-815 $56

816+ $50


If a person enters say, 300 units, the next cell would return "$86".


Thanks.


Fred
 
Fred,


This can be solved using VLOOKUP but for that you will have to make small adjustment to your data, if that is possible. Take the lower limit in the interval in column A and price/unit($) in Column B as:


Qty(A) Price(B)

1-----100

51------98

111------95

241------91

296------86

376------80

551------73

626------65

746------56

816------50


I am assuming person will enter the quantity in cell D2, you can use the formaula as E2: =VLOOKUP(D2,A1:B10,2,TRUE)


It will pick the price/unit associate for the entered qty.


Hope this will help if the adjustment is possible.
 
I didn't know you can put "True" on the function. I have always used "false" but never learnt when to use "True".


Thanks, PSG!
 
Fred: "FALSE" is for Exact match while we use "TRUE" for approximate match!


I am glad to be of help to you. :)
 
Back
Top