How to get pricing with multi tiered pricing from workbook

Posted on 2012-03-15
Last Modified: 2012-03-15
I have a worksheet that has the current prices for each item. This worksheet has various prices based on the size of your order for that product.
Here are the columns:
Min Order (the minimum that you can order of that item)
Base Price (price you pay if you order the min up to the next level)
Discount1 (QTY (the number of items to get the first price break)
Discount1 (Price Price for the second tier of pricing)
Discount2 (QTY (the number of items to get the second price break)
Discount2 (Price Price for the third tier of pricing)
Discount3 (QTY (the number of items to get the third price break)
Discount3 (Price Price for the forth tier of pricing)

Sample of data:

What I would like to do is to have a module that would return the price for the number of items ordered for a line item on an orderform

I want to order 14 Wig1, it would return 9.60
If I place an order for 2 wig5, up comes a messagebox that lets me know the minimum
Then if I correct that, and order 6 wig5 if would return 4.85
And if I order 1000 wig4, it would return 1.75

How would I code this? I can use VLOOKUP easy enough to get the BASE price, but not sure how to get to and test for the other columns.

Question by:Bruj
  • 2
LVL 39

Accepted Solution

als315 earned 250 total points
ID: 37725972

Assisted Solution

reitzen earned 250 total points
ID: 37725988
=IF(C10<VLOOKUP(B10,tbl,3,FALSE),"Minimum required "&VLOOKUP(B10,tbl,3,FALSE),IF(AND(C10>=VLOOKUP(B10,tbl,3,FALSE),C10<VLOOKUP(B10,tbl,5,FALSE)),VLOOKUP(B10,tbl,4,FALSE),IF(AND(C10>=VLOOKUP(B10,tbl,5,FALSE),C10<VLOOKUP(B10,tbl,7,FALSE)),VLOOKUP(B10,tbl,6,FALSE),IF(AND(C10>=VLOOKUP(B10,tbl,7,FALSE),C10<VLOOKUP(B10,tbl,9,FALSE)),VLOOKUP(B10,tbl,8,FALSE),VLOOKUP(B10,tbl,10,FALSE)))))

Open in new window

Using the 10 columns you described, this will return the correct value based upon the quantity ordered.

I used nested IF's to test if the quantity ordered was between MinOrder and Disc1, then Disc1 and Disc2, etc.

There is probably a more elegant approach to this using an array formula or something, but that would be above my pay grade.  :-P

Good luck

Expert Comment

ID: 37725994
PS.  I used a named range "tbl" to represent your data table A1:J6

Author Closing Comment

ID: 37726605
I was looking for a function, which you guys guided me on how to write it!
It is here:
Public Function GetPrice(myItem As String, myQTY As Long) As Double
Dim myRange As Range

Set myRange = ThisWorkbook.Sheets("Sheet1").Range("A:k")
If myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 9, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 10, False)
ElseIf myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 7, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 8, False)
ElseIf myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 5, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 6, False)
ElseIf myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 3, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 4, False)
MsgBox "Minimum order is " & Application.WorksheetFunction.VLookup(myItem, myRange, 3, False)
End If

End Function

Open in new window

Now I just need to add some error checking (like if part is not found, or an item does not have a discounted price etc....)


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to convert a multi-page PDF file into multiple image files, with one image file created for each page of the PDF. It does this by utilizing an excellent, free software package called GraphicsMagick. The solution is amazingly s…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now